date query when date field is char and mmyy format

Hi All,
I have a field which stored date as char datatype in mmyy format.
Acutally, I have inherited this and there is no way I can change the
design of the database.
Now, I needed to run a qeury between two months (from December 2005 to
January 2006).
My query was:
select * from table1 where claim_date between '1205' and 0106'
This did not give me any records whereas, I know there are records for
the month of December (claim_date : 1205)
I understand that 0106 is smaller than 1205 and that is the main reason
that I am not getting any records. Because if I change it to
select * from table1 where claim_date between '0106' and '1205', then
it gives me the results I want. I feel this is not right.
My question is how can I handle this problem in the query, so that I
can really use the date range as claim_date between '1205' and '0106'.
CREATE TABLE [dbo].[table1] (
[Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Insert table1
select 'D124', '1205', 'A'
union all
select 'D243', '0106', 'A'
Thanks a million in advance.
Best regards,
MamunWithout changing the table structure and storing the right data in the right
format, don't expect this to have sizzling performance.
SELECT * FROM
(
SELECT
*,
claim_date_calc = '20'+RIGHT(claim_date,2)+LEFT(claim_date
,2)+'01'
FROM
table1
WHERE
ISDATE('20'+RIGHT(claim_date,2)+LEFT(cla
im_date,2)+'01') = 1
)
x
WHERE CONVERT(SMALLDATETIME, claim_date_calc) BETWEEN '20051201' AND
'20060101';
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1139340637.499729.6330@.f14g2000cwb.googlegroups.com...
> Hi All,
> I have a field which stored date as char datatype in mmyy format.
> Acutally, I have inherited this and there is no way I can change the
> design of the database.
> Now, I needed to run a qeury between two months (from December 2005 to
> January 2006).
> My query was:
> select * from table1 where claim_date between '1205' and 0106'
> This did not give me any records whereas, I know there are records for
> the month of December (claim_date : 1205)
> I understand that 0106 is smaller than 1205 and that is the main reason
> that I am not getting any records. Because if I change it to
> select * from table1 where claim_date between '0106' and '1205', then
> it gives me the results I want. I feel this is not right.
> My question is how can I handle this problem in the query, so that I
> can really use the date range as claim_date between '1205' and '0106'.
> CREATE TABLE [dbo].[table1] (
> [Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> Insert table1
> select 'D124', '1205', 'A'
> union all
> select 'D243', '0106', 'A'
> Thanks a million in advance.
> Best regards,
> Mamun
>|||where substring(Claim_Date,3,2)+substring(Clai
m_Date,1,2)
between '0512' and '0601'|||Both solutions worked great. I was not looking at the performance but
to get the results.
Thanks a ton to both of you.
best regards,
mamun|||Another approach would be to convert the values for claim_date into honest
datetimes, and compare to other datetime values
SELECT Claim_Number, Claim_Date, Prime_ABC
FROM table1
WHERE
CONVERT(datetime, RIGHT(Claim_Date,2) + LEFT(Claim_Date,2) + '01', 12)
BETWEEN '20051201' AND '20060101'
With all respect to Alexander, using BETWEEN with character datatypes gives
me the heebee jeebees.
"microsoft.public.dotnet.languages.vb" wrote:

> Hi All,
> I have a field which stored date as char datatype in mmyy format.
> Acutally, I have inherited this and there is no way I can change the
> design of the database.
> Now, I needed to run a qeury between two months (from December 2005 to
> January 2006).
> My query was:
> select * from table1 where claim_date between '1205' and 0106'
> This did not give me any records whereas, I know there are records for
> the month of December (claim_date : 1205)
> I understand that 0106 is smaller than 1205 and that is the main reason
> that I am not getting any records. Because if I change it to
> select * from table1 where claim_date between '0106' and '1205', then
> it gives me the results I want. I feel this is not right.
> My question is how can I handle this problem in the query, so that I
> can really use the date range as claim_date between '1205' and '0106'.
> CREATE TABLE [dbo].[table1] (
> [Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> Insert table1
> select 'D124', '1205', 'A'
> union all
> select 'D243', '0106', 'A'
> Thanks a million in advance.
> Best regards,
> Mamun
>

Date Format Question

Hi all - I have a question regarding formating getdate(). Below is my query.

SELECT

cast(datepart(yyyy, getdate()) as char(4))

+'-'+ cast(datepart(mm,getdate()) as char(2))

+'-'+ cast(datepart(dd, getdate()) as char(2))

Result

2007-5 -30

The result I would like is:

2007-05-30

Can anyone help me with this?

Thanks in Advance.

You could use:

Code Snippet

SELECT convert( varchar(10), getdate(), 120 )

-
2007-05-30

Refer to Books Online, Topic: 'Cast and Convert' for the usage of the 'style' codes (the 120 above).

Date Format for a String

Using SQL Server 2000 in a view, if the data is Char and six
characters like: 081564
How can you convert it to a date that looks like: 08/15/1964 and make
it a date value?
Thanks for any suggestions!
RBollingAssuming mdy dateformat and english language settings...
DECLARE @.foo VARCHAR(32);
SET @.foo = '081564';
SET @.foo = CONVERT(SMALLDATETIME, LEFT(@.foo, 2) + '/' + SUBSTRING(@.foo, 3,2)
+ '/' + RIGHT(@.foo, 2));
SELECT @.foo;
Otherwise, you may need more converts in there...
"robboll" <robboll@.hotmail.com> wrote in message
news:1188925487.090517.43360@.d55g2000hsg.googlegroups.com...
> Using SQL Server 2000 in a view, if the data is Char and six
> characters like: 081564
> How can you convert it to a date that looks like: 08/15/1964 and make
> it a date value?
> Thanks for any suggestions!
> RBolling
>|||> How can you convert it to a date that looks like: 08/15/1964 and make
> it a date value?
Above is a contradiction in terms. First, there's no "date" datatype in SQL Server.
Perhaps you mean datetime? If so, datetime doesn't have any format attached to it. It is stored
internally as a number of 0's and 1's. The format that *you* see when you look at it is determined
by the client application. You can use the CONVERT function to convert it to a string with a certain
format and look at that string, but now it isn't datetime anymore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"robboll" <robboll@.hotmail.com> wrote in message
news:1188925487.090517.43360@.d55g2000hsg.googlegroups.com...
> Using SQL Server 2000 in a view, if the data is Char and six
> characters like: 081564
> How can you convert it to a date that looks like: 08/15/1964 and make
> it a date value?
> Thanks for any suggestions!
> RBolling
>

date format

How can I strip the time from datetime field without converting to Char or
Vchar?
You can't. DATETIME and SMALLDATETIME always store both date and time.
What you can do is set the time to midnight. For example:
SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP),0)
David Portas
SQL Server MVP
|||Date already exists so I am not sure how to utlize
SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP),0)
to the existing date field.
"David Portas" wrote:

> You can't. DATETIME and SMALLDATETIME always store both date and time.
> What you can do is set the time to midnight. For example:
> SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP),0)
> --
> David Portas
> SQL Server MVP
> --
>
>

Date format

Hi all,
I want to return the correct format
print CONVERT(CHAR(26),GETDATE(),22)
returns
09/28/05 12:25:18 PM
would like to return it like this 09/28/2005 12:25:18 PM
How please?
thanks
gvHi
22 is not a familiary style I recognised and it does not seem to be
documented in books online! Normally you would expect to add 100 to the styl
e
to get a four digit year, but you may want to try something like:
SELECT STUFF(CONVERT(CHAR(26),GETDATE(),22),7,2
,CAST(YEAR(GETDATE()) AS
CHAR(4)))
John
"gv" wrote:

> Hi all,
> I want to return the correct format
> print CONVERT(CHAR(26),GETDATE(),22)
> returns
> 09/28/05 12:25:18 PM
> would like to return it like this 09/28/2005 12:25:18 PM
> How please?
> thanks
> gv
>
>|||CONVERT(CHAR(26),GETDATE(),122)
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"gv" <viatorg@.musc.edu> wrote in message
news:O%23J73pExFHA.3856@.tk2msftngp13.phx.gbl...
> Hi all,
> I want to return the correct format
> print CONVERT(CHAR(26),GETDATE(),22)
> returns
> 09/28/05 12:25:18 PM
> would like to return it like this 09/28/2005 12:25:18 PM
> How please?
> thanks
> gv
>|||Using 122 I get error
Server: Msg 281, Level 16, State 1, Line 3
122 is not a valid style number when converting from datetime to a character
string.
Server: Msg 8114, Level 16, State 1, Line 3
Error converting data type datetime to varchar.
thanks
gv
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OdAPe2ExFHA.3000@.TK2MSFTNGP12.phx.gbl...
> CONVERT(CHAR(26),GETDATE(),122)
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "gv" <viatorg@.musc.edu> wrote in message
> news:O%23J73pExFHA.3856@.tk2msftngp13.phx.gbl...
>|||thanks
gv
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:29AE5D05-1362-46AC-9A48-230CBF9BA4B3@.microsoft.com...
> Hi
> 22 is not a familiary style I recognised and it does not seem to be
> documented in books online! Normally you would expect to add 100 to the
> style
> to get a four digit year, but you may want to try something like:
> SELECT STUFF(CONVERT(CHAR(26),GETDATE(),22),7,2
,CAST(YEAR(GETDATE()) AS
> CHAR(4)))
> John
> "gv" wrote:
>

date format

How can I strip the time from datetime field without converting to Char or
Vchar?You can't. DATETIME and SMALLDATETIME always store both date and time.
What you can do is set the time to midnight. For example:
SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP),0)
--
David Portas
SQL Server MVP
--|||Date already exists so I am not sure how to utlize
SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP),0)
to the existing date field.
"David Portas" wrote:
> You can't. DATETIME and SMALLDATETIME always store both date and time.
> What you can do is set the time to midnight. For example:
> SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP),0)
> --
> David Portas
> SQL Server MVP
> --
>
>

date format

How can I strip the time from datetime field without converting to Char or
Vchar?You can't. DATETIME and SMALLDATETIME always store both date and time.
What you can do is set the time to midnight. For example:
SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMES
TAMP),0)
David Portas
SQL Server MVP
--|||Date already exists so I am not sure how to utlize
SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMES
TAMP),0)
to the existing date field.
"David Portas" wrote:

> You can't. DATETIME and SMALLDATETIME always store both date and time.
> What you can do is set the time to midnight. For example:
> SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMES
TAMP),0)
> --
> David Portas
> SQL Server MVP
> --
>
>

Date Conversion help

Hi,

I have my dates in DB2 source in two formats -

Format 1 - char(5) - Example - 10305. 1 indicates century,03 indicates year and 05 indicates month. The day is not stored. So this is 2003,May 1

Format 2 - char(7) - Example - 1030525. 1 indicates century,03 indicates year, 05 indicates month,25 indicates day. 2003,May 25

I want to convert the above two formats to SQL Server smalldatetime and I only need the DATEPART. The date needs to be in the format mm/dd/yyyy. The default day would be 01 when the day is not specified.

If the format is 00305 then the 0 indicated 19th century. So this is 1903, May 1.

Any help is appreciated.

Thanks,

Vivek1 indicates century? What century is it supposed to indicate? Are we talking the year 1903? I am assuming it is supposed to be 1=2000 in the code below:

CREATE FUNCTION fn_convert_DB@._date (@.db2_date VARCHAR(7))

RETURNS SMALLDATETIME AS

DECLARE @.new_date AS SMALLDATETIME

IF LEN(@.db2_date) = 5
SELECT @.new_date = CAST(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 1 AS VARCHAR(1)) + '0' + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

IF LEN(@.db2_date) = 7
SELECT @.new_date = CAST(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 1 AS VARCHAR(1)) + '0' + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

RETURN @.new_date|||Yes 1 indicates century 2000 and 0 indicated 1900. So 10305 would be 2003. From first 3 digits. And 19505 would be 1995.|||You should be able to use the function inline in your queries:

SELECT dbo.fn_convert_db2_date(the_db2_date_field)

will return the converted SMALLDATETIME field|||Hi,

I am not able to convert 09525 using the function. Also the date I need is of the format - mm/dd/yyyy ex: 5/21/2003. When I execute the function for '10325', I get 2004-05-01 00:00:00. I do not need the time part. Also the format is different.

Thanks|||The date format is always store in the same fashion in SQL Server. What you want to do is alter the format when you display the date in your UI/report/whatever it is.

CONVERT(the_date_field, 101) will give you a VARCHAR of the date in mm/dd/yyyy.

'10325', according to your definition, would be Year=2003, Month = 25.

Reworking the year conversion. Sorry about the shoddy workmanship there.|||CREATE FUNCTION fn_convert_DB@._date (@.db2_date VARCHAR(7))

RETURNS SMALLDATETIME AS

DECLARE @.new_date AS SMALLDATETIME

IF LEN(@.db2_date) = 5 AND ISDATE(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2))=1
SELECT @.new_date = CAST(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2) AS SMALLDATETIME)

IF LEN(@.db2_date) = 7 AND ISDATE(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2))=1
SELECT @.new_date = CAST(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

RETURN @.new_date|||New function checks for proper date format before converting, and returns NULL if format is wrong. Oh, and it deals properly with centuries.

Date Conversion

I need some help with Date Conversions. I am getting the following error
when I run the query below.
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
declare @.inputdate char(10)
declare @.tempdate datetime
declare @.validdate char(10)
SET @.inputdate = '15/05/2004'
SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The Company -
Dev$Base Calendar Change]
WHERE Date = @.InputDate and Nonworking = '1')
if @.tempdate is not null
SET @.validdate = '0' else SET @.validdate = '1'
select @.validdateHi,
Infact you dont require a conversion in that place, because it seems the
Date field in the table is datetime datatype and @.tempdate variable also a
datetime datype.
In this case you dont require a convert.
Incase you need to convert please use 101 instead of 103.
Thanks
Hari
MCDBA
"Sarah" <skingswell@.donotreply.com> wrote in message
news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
> I need some help with Date Conversions. I am getting the following error
> when I run the query below.
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> declare @.inputdate char(10)
> declare @.tempdate datetime
> declare @.validdate char(10)
> SET @.inputdate = '15/05/2004'
> SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The
Company -
> Dev$Base Calendar Change]
> WHERE Date = @.InputDate and Nonworking = '1')
> if @.tempdate is not null
> SET @.validdate = '0' else SET @.validdate = '1'
> select @.validdate
>|||The problem is how the
SET @.inputdate = '15/05/2004
is converted. This is based on the Lanaguage of the Login (unless overridden)
You can either override the date setting,
SET DATEFORMAT dm
Or use a neutral date formats (ISO and ISO8601
SET @.inputdate = '20040515' -- IS
SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
When using ISO8601, the 'T' must be there or else you will get very stange results
With a DMY connection, it reads it as YDM, which no one uses
With a MDY connection, it reads it as YMD, with is the ODBC standard|||The problem that I am having is the format of the date because if I actually
look at the Date values in the table with the enterprise manager they are in
the same format as the @.tempdate variable
25/05/2004
but the select query returns 2004-05-25 00:00:00. This is why I am
converting the select results. I still cannot get this working.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OUg2CeR$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Infact you dont require a conversion in that place, because it seems the
> Date field in the table is datetime datatype and @.tempdate variable also a
> datetime datype.
> In this case you dont require a convert.
> Incase you need to convert please use 101 instead of 103.
> Thanks
> Hari
> MCDBA
> "Sarah" <skingswell@.donotreply.com> wrote in message
> news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
> > I need some help with Date Conversions. I am getting the following
error
> > when I run the query below.
> >
> > The conversion of a char data type to a datetime data type resulted in
an
> > out-of-range datetime value.
> >
> > declare @.inputdate char(10)
> > declare @.tempdate datetime
> > declare @.validdate char(10)
> >
> > SET @.inputdate = '15/05/2004'
> >
> > SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The
> Company -
> > Dev$Base Calendar Change]
> > WHERE Date = @.InputDate and Nonworking = '1')
> > if @.tempdate is not null
> > SET @.validdate = '0' else SET @.validdate = '1'
> > select @.validdate
> >
> >
>|||Excellent. Thanks very much. I used the SET DATEFORMAT dmy in the query and
it works perfectly.
"Al" <al_davie@.hotmail.com> wrote in message
news:E5B3103E-BDCC-4132-9006-7E59B6615214@.microsoft.com...
> The problem is how the
> SET @.inputdate = '15/05/2004'
> is converted. This is based on the Lanaguage of the Login (unless
overridden).
> You can either override the date setting,
> SET DATEFORMAT dmy
> Or use a neutral date formats (ISO and ISO8601)
> SET @.inputdate = '20040515' -- ISO
> SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
> When using ISO8601, the 'T' must be there or else you will get very stange
results.
> With a DMY connection, it reads it as YDM, which no one uses.
> With a MDY connection, it reads it as YMD, with is the ODBC standard.
>

Date Conversion

I need some help with Date Conversions. I am getting the following error
when I run the query below.
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
declare @.inputdate char(10)
declare @.tempdate datetime
declare @.validdate char(10)
SET @.inputdate = '15/05/2004'
SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The Compan
y -
Dev$Base Calendar Change]
WHERE Date = @.InputDate and Nonworking = '1')
if @.tempdate is not null
SET @.validdate = '0' else SET @.validdate = '1'
select @.validdateHi,
Infact you dont require a conversion in that place, because it seems the
Date field in the table is datetime datatype and @.tempdate variable also a
datetime datype.
In this case you dont require a convert.
Incase you need to convert please use 101 instead of 103.
Thanks
Hari
MCDBA
"Sarah" <skingswell@.donotreply.com> wrote in message
news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
> I need some help with Date Conversions. I am getting the following error
> when I run the query below.
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> declare @.inputdate char(10)
> declare @.tempdate datetime
> declare @.validdate char(10)
> SET @.inputdate = '15/05/2004'
> SET @.tempdate = (SELECT convert (char(10), Date, 103) FROM [The
Company -
> Dev$Base Calendar Change]
> WHERE Date = @.InputDate and Nonworking = '1')
> if @.tempdate is not null
> SET @.validdate = '0' else SET @.validdate = '1'
> select @.validdate
>|||The problem is how the
SET @.inputdate = '15/05/2004'
is converted. This is based on the Lanaguage of the Login (unless overridden
).
You can either override the date setting,
SET DATEFORMAT dmy
Or use a neutral date formats (ISO and ISO8601)
SET @.inputdate = '20040515' -- ISO
SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
When using ISO8601, the 'T' must be there or else you will get very stange r
esults.
With a DMY connection, it reads it as YDM, which no one uses.
With a MDY connection, it reads it as YMD, with is the ODBC standard.|||The problem that I am having is the format of the date because if I actually
look at the Date values in the table with the enterprise manager they are in
the same format as the @.tempdate variable
25/05/2004
but the select query returns 2004-05-25 00:00:00. This is why I am
converting the select results. I still cannot get this working.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OUg2CeR$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Infact you dont require a conversion in that place, because it seems the
> Date field in the table is datetime datatype and @.tempdate variable also a
> datetime datype.
> In this case you dont require a convert.
> Incase you need to convert please use 101 instead of 103.
> Thanks
> Hari
> MCDBA
> "Sarah" <skingswell@.donotreply.com> wrote in message
> news:u6RnNUR$DHA.220@.TK2MSFTNGP09.phx.gbl...
error
an
> Company -
>|||Excellent. Thanks very much. I used the SET DATEFORMAT dmy in the query and
it works perfectly.
"Al" <al_davie@.hotmail.com> wrote in message
news:E5B3103E-BDCC-4132-9006-7E59B6615214@.microsoft.com...
> The problem is how the
> SET @.inputdate = '15/05/2004'
> is converted. This is based on the Lanaguage of the Login (unless
overridden).
> You can either override the date setting,
> SET DATEFORMAT dmy
> Or use a neutral date formats (ISO and ISO8601)
> SET @.inputdate = '20040515' -- ISO
> SET @.inputdate = '2004-05-15T00:00.000' -- ISO8601
> When using ISO8601, the 'T' must be there or else you will get very stange
results.
> With a DMY connection, it reads it as YDM, which no one uses.
> With a MDY connection, it reads it as YMD, with is the ODBC standard.
>