Date Format

8 Sep 2005 1:47 PM

A.B.

Will the following statement give me the date in the format of dd/mm/yyyy?
Set @begindate = Convert(Datetime, @begindate, 103)

8 Sep 2005 2:06 PM

A.B.

Also is it possible to do a search of a range on a field that is defined as
varchar. Example: data is imported into a table from a text file. The data
contains the date of when a project finished. The date is imported as
varchar. I need to know the projects that finished in a given week. The date
string is imported with the format dd/mm/yyyy.

Create Table Project
(ProjectID int Primary Key,
DateStart Varchar(10),
DateStop Varchar(10),
ProjectLeadID
)

Select *
From Project
Where DateStart >= 'date'
And DateStop <= 'another date'
Show quote

"A.B." wrote:

> Will the following statement give me the date in the format of dd/mm/yyyy?
> Set @begindate = Convert(Datetime, @begindate, 103)

8 Sep 2005 2:49 PM

Alejandro Mesa

Try,

declare @sd datetime
declare @ed datetime

set @sd = '20050101'
set @ed = '20050908'

Select *
From Project
Where
right(DateStart, 4) + substring(DateStart, 4, 2) + left(DateStart, 2) >=
@sd
And right(DateStop, 4) + substring(DateStop, 4, 2) + left(DateStop, 2) <
dateadd(day, 1, @ed)

Do not expect that sql server perform an index seek if there is a index by
DateStart and / or DateStop.

AMB

Show quote

"A.B." wrote:

> Also is it possible to do a search of a range on a field that is defined as
> varchar. Example: data is imported into a table from a text file. The data
> contains the date of when a project finished. The date is imported as
> varchar. I need to know the projects that finished in a given week. The date
> string is imported with the format dd/mm/yyyy.
>
> Create Table Project
> (ProjectID int Primary Key,
> DateStart Varchar(10),
> DateStop Varchar(10),
> ProjectLeadID
> )
>
> Select *
> From Project
> Where DateStart >= 'date'
> And DateStop <= 'another date'
> "A.B." wrote:
>
> > Will the following statement give me the date in the format of dd/mm/yyyy?
> > Set @begindate = Convert(Datetime, @begindate, 103)

8 Sep 2005 2:51 PM

Laura

In order to search on a range of dates, you'll need to convert the varchar
columns to datetime. Something like this:

Select *
From Project
Where convert(datetime, DateStart) >= 'date'
And convert(datetime, DateStop) <= 'another date'

Show quote

"A.B." wrote:

> Also is it possible to do a search of a range on a field that is defined as
> varchar. Example: data is imported into a table from a text file. The data
> contains the date of when a project finished. The date is imported as
> varchar. I need to know the projects that finished in a given week. The date
> string is imported with the format dd/mm/yyyy.
>
> Create Table Project
> (ProjectID int Primary Key,
> DateStart Varchar(10),
> DateStop Varchar(10),
> ProjectLeadID
> )
>
> Select *
> From Project
> Where DateStart >= 'date'
> And DateStop <= 'another date'
> "A.B." wrote:
>
> > Will the following statement give me the date in the format of dd/mm/yyyy?
> > Set @begindate = Convert(Datetime, @begindate, 103)

8 Sep 2005 9:51 PM

Hugo Kornelis

On Thu, 8 Sep 2005 07:06:24 -0700, A.B. wrote:

>Also is it possible to do a search of a range on a field that is defined as
>varchar. Example: data is imported into a table from a text file. The data
>contains the date of when a project finished. The date is imported as
>varchar. I need to know the projects that finished in a given week. The date
>string is imported with the format dd/mm/yyyy.
>
>Create Table Project
>(ProjectID int Primary Key,
>DateStart Varchar(10),
>DateStop Varchar(10),
>ProjectLeadID
>)
(snip)

Hi A.B.,

Make sure you check the validity of the date when doing the import. Than
convert it to datetime format. That will make all queries on your dates
lots easier.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

8 Sep 2005 2:16 PM

Alejandro Mesa

A.B.,

SQL Server does not store datetime values in a specific format. See
"datetime and smalldatetime" in BOL for more info.

AMB

Show quote

"A.B." wrote:

> Will the following statement give me the date in the format of dd/mm/yyyy?
> Set @begindate = Convert(Datetime, @begindate, 103)

8 Sep 2005 2:56 PM

A.B.

I am using the statement to set a variable later in my query I am going to
use the variable to pass that date to a SP that I wrote. But I need to the
format of the date to be dd/mm/yyyy.
Alter Procedure ButtonPushesRan
@begindate     VarChar(11),
@enddate                    VarChar(11)
AS
Set @begindate = Convert(Datetime, @begindate, 103)
Set @enddate = Convert(Datetime, @enddate, 103)

SELECT DISTINCT TOP 100 PERCENT LotID, TDateStop
FROM         dbo.LotInfoData
WHERE     TDateStop <= @begindate
    or TDateStop >= @endDate
ORDER BY LotID
Show quote

"Alejandro Mesa" wrote:

> A.B.,
>
> SQL Server does not store datetime values in a specific format. See
> "datetime and smalldatetime" in BOL for more info.
>
>
> AMB
>
> "A.B." wrote:
>
> > Will the following statement give me the date in the format of dd/mm/yyyy?
> > Set @begindate = Convert(Datetime, @begindate, 103)

8 Sep 2005 9:53 PM

Hugo Kornelis

On Thu, 8 Sep 2005 07:56:04 -0700, A.B. wrote:

Show quote

>I am using the statement to set a variable later in my query I am going to
>use the variable to pass that date to a SP that I wrote. But I need to the
>format of the date to be dd/mm/yyyy.
>Alter Procedure ButtonPushesRan
>@begindate     VarChar(11),
>@enddate                    VarChar(11)
>AS
>Set @begindate = Convert(Datetime, @begindate, 103)
>Set @enddate = Convert(Datetime, @enddate, 103)
>
>SELECT DISTINCT TOP 100 PERCENT LotID, TDateStop
>FROM         dbo.LotInfoData
>WHERE     TDateStop <= @begindate
>    or TDateStop >= @endDate
>ORDER BY LotID

Hi A.B.,

If both the TDateStop column and your variable are defined as varchar,
you'll be doing string comparisons. That means that '01/09/2006' will be
considered to be less than '31/08/2005'. I doubt that is what you want
to achieve. As I said in my other post - convert the imported data to
datetime and store it as such.

Also, remove the TOP 100 PERCENT from the SELECT statement - a SELECT
statement will return all rows by default.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

8 Sep 2005 2:49 PM

Laura

I believe what you're looking for is:

Set @begindate = Convert(varchar, @begindate, 103)

Show quote

"A.B." wrote:

> Will the following statement give me the date in the format of dd/mm/yyyy?
> Set @begindate = Convert(Datetime, @begindate, 103)