Dates before 1900 - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Dates before 1900

Author

Topic

dataczar
Starting Member

18 Posts

Posted - 2008-12-03 : 15:36:11

I need to take a date in decimal format that is before 1900 and convert it to datetime.

I have the following.

select cast((cast(myfield as char(8)) as datetime)

I am getting an error.

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

Any help would be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 15:37:57

Datetime can only deal with years back to and including 1753.

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

dataczar
Starting Member

18 Posts

Posted - 2008-12-03 : 15:43:37

My dates that are giving me issues are between 1888 and 1900.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-03 : 15:44:05

Show us some sample values that are throwing that error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 16:48:46

I'll bet you have some nulls in there or something that cannot easily be converted to a datetime.


SELECT CAST('18910911' AS DATETIME)

yields:


1891-09-11 00:00:00.000

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 16:50:52

NULLs can be converted to DATETIME.

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

dataczar
Starting Member

18 Posts

Posted - 2008-12-03 : 16:57:13

My issue was that there was this date 19000229, which is not a leap year.

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 16:58:29

yuh, true. I will amend my statement then to "something that cannot easily be converted to a datetime."

try this:


SELECT myfield FROM table where ISDATE(myfield) = 0;

this should show you any records that do not evaluate to convertible datetime expressions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 23:39:38

quote:
Originally posted by dataczar

My issue was that there was this date 19000229, which is not a leap year.

thats indeed an invalid date. thats why the error.

see this too

select isdate('19000229')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources