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.
>