convert character date to datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

convert character date to datetime

Author

Topic

AnnieM
Starting Member

6 Posts

Posted - 2009-08-17 : 18:32:17

Hi,

I have a date field that's in a varchar(50) field.
The format is YYYY-MM-DD oo:oo:oo

As an example, 1955-07-15 00:00:00

I thought this would convert, but it's not working:
CONVERT(datetime,LAW.birthdate,20)

Much thanks!
Annie

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-17 : 18:55:42

works for me. what is the error message you get? perhaps there is a value in table that won't convert, but the one in your example does.


create table #t (dt varchar(50))

insert #t values ('1955-07-15 00:00:00')

select convert(datetime, dt, 20) from #t

drop table #t

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-17 : 18:57:45

by the way, the style is for when converting to character data types. not needed when converting to datetime or smalldatetime

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 19:43:55

Do you have other characters than zero in your time part?

Run this query

select * from law where isdate(birthdate) = 0

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-18 : 02:51:46

If all dates are in the format YYYY-MM-DD oo:oo:oo

select * from law where isdate(birthdate) = 0 and len(birthdate)=19

Madhivanan

Failing to plan is Planning to fail

AnnieM
Starting Member

6 Posts

Posted - 2009-08-18 : 10:32:44

Thanks soooo much!

The field value triggering the error was '1700-01-01 00:00:00'
that looks like a date to me but apparantly not...

The error message is
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

I worked around by using the ISDATE function and so no longer get the error:
LEFT JOIN ELASource.dbo.LAW_EMP LAW
ON WEB.DPSID = LAW.DPSID AND
isdate(birthdate) = 1

So I am happy!
Thanks again, this board is great