Converting to datetime...

Hello..
I am having a difficult time trying to get SQL Server to convert the
following date:
22-08-2004 00:00:00
I have tried to convert and cast and I get the following error message:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
Any help would be most appreciative.
Thank you,
Brett
P.S.
I am using SQL Server 2000How are you doing this convert ? This works for me:
Select convert(varchar(20),'22-08-2004 00:00:00',102)
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Brett Davis" <bdavis123@.cox.net> schrieb im Newsbeitrag
news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> Hello..
> I am having a difficult time trying to get SQL Server to convert the
> following date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>|||You need to tell SQL Server more about the pattern used for the datetime. Tr
y
this:
Select Convert(DateTime, '22-08-2004 00:00:00', 105)
Without specifying the date format, I believe that SQL uses the format from
the
database's collation or perhaps the server's regional settings.
HTH
Thomas
"Brett Davis" <bdavis123@.cox.net> wrote in message
news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> Hello..
> I am having a difficult time trying to get SQL Server to convert the follo
wing
> date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
an
> out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>|||Use styles 112 or 126. See CONVERT in BOL.
Example:
select cast('20040822' as datetime)
select cast('2004-08-22T00:00:00.000' as datetime)
go
AMB
"Brett Davis" wrote:

> Hello..
> I am having a difficult time trying to get SQL Server to convert the
> following date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>
>|||Thomas,
You use the style parameter when converting from datetime to varchar / char
and not the opposite. All these statements will give same result.
select convert(datetime, '20050427', 105)
select convert(datetime, '20050427', 112)
select convert(datetime, '20050427', 126)
select convert(datetime, '20050427')
AMB
"Thomas" wrote:

> You need to tell SQL Server more about the pattern used for the datetime.
Try
> this:
> Select Convert(DateTime, '22-08-2004 00:00:00', 105)
> Without specifying the date format, I believe that SQL uses the format fro
m the
> database's collation or perhaps the server's regional settings.
>
> HTH
>
> Thomas
>
> "Brett Davis" <bdavis123@.cox.net> wrote in message
> news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
>
>|||I think we might be saying the same thing. By passing the style parameter, y
ou
are giving SQL information about the format of the string.

>All these statements will give same result.
> select convert(datetime, '20050427', 105)
> select convert(datetime, '20050427', 112)
> select convert(datetime, '20050427', 126)
> select convert(datetime, '20050427')
But these do not:
1. select convert(datetime, '22-08-2004 00:00:00', 105)
2. select convert(datetime, '22-08-2004 00:00:00', 112)
3. select convert(datetime, '22-08-2004 00:00:00', 126)
4. select convert(datetime, '22-08-2004 00:00:00',)
Only the first one successfully parses the string into a datetime. The other
s
fail with a conversion error because the system thinks that the first digits
are
the month instead of the day.
I'll grant you that passing the ISO format (yyyymmdd) would be the best way
to
avoid all of these problems.
Thomas|||You are right.
AMB
"Thomas" wrote:

> I think we might be saying the same thing. By passing the style parameter,
you
> are giving SQL information about the format of the string.
>
> But these do not:
> 1. select convert(datetime, '22-08-2004 00:00:00', 105)
> 2. select convert(datetime, '22-08-2004 00:00:00', 112)
> 3. select convert(datetime, '22-08-2004 00:00:00', 126)
> 4. select convert(datetime, '22-08-2004 00:00:00',)
> Only the first one successfully parses the string into a datetime. The oth
ers
> fail with a conversion error because the system thinks that the first digi
ts are
> the month instead of the day.
> I'll grant you that passing the ISO format (yyyymmdd) would be the best wa
y to
> avoid all of these problems.
>
> Thomas
>
>