Invalid date filtering - 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

General SQL Server Forums

New to SQL Server Programming

Invalid date filtering

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-05-31 : 20:39:57

Hi,

There is a date field present in table1 as character field in the format of YYYYMMDD. I need to convert this to datetime
and load it into table2. But there are some invalid dates in that field of table1.While converting and loading to table2 i need to put NULLS for the invalid dates and continue loading the rest of them into table2.

Eg: Dates in Table1:
19860930,
0,
999,
19820925 etc.

When i load into table2 it should be loaded as
1986-09-30 00:00:00.000
NULL,
NULL,
1982-09-25 00:00:00.000

How can i add this conditon to the query. Currently i have the below selection query:

SELECT CAST(LTRIM(RTRIM(ODRDT)) as datetime) AS ORDERDT
FROM Table1

Please advise

Thanks,

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-05-31 : 21:02:01

Use the ISDATE function to test for valid dates...

--Jeff Moden

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-01 : 00:01:01

Select Cast(Case when IsDate(ODRDT) = 1 then ODRDT else Null end as Datetime) from Table1

--------------------------------------------------
S.Ahamed

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-01 : 08:45:12

Note that ISDATE is not reliable like ISNUMERIC

Select isdate(2005),isdate('2006')

So, if all date values are stored in YYYYMMDD format, you need to check it's length also

Select Cast(Case when len(ODRDT)=8 and IsDate(ODRDT) = 1 then ODRDT else Null end as Datetime) from Table1

Madhivanan

Failing to plan is Planning to fail

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-06-01 : 15:09:08

Hi,

I am getting error "Arithmetic overflow error converting expression to data type datetime." while checking for date and length as in above query. (The ODRDT is of decimal data type...). When i am seperately testing for ISDATE or Len, it does not give any error, but when CAST as date time it gives error..

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-06-01 : 19:35:11

Hi,

I fixed it. This is the code:

Select Cast(
cast (
Case when len(ODRDT)=8 and IsDate(ODRDT) = 1 then ODRDT else Null end
as nvarchar(8))
as Datetime)
from Table1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-02 : 01:33:11

Do you need nvarchar?
casting to Varchar also will work

Select Cast(
cast (
Case when len(ODRDT)=8 and IsDate(ODRDT) = 1 then ODRDT else Null end
as varchar(8))
as Datetime)
from Table1

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-02 : 01:46:26

Also, if you have any columns where dates are stored in dmy format (with not fixed length), you can make use of this function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-06-04 : 08:41:31

quote:
Originally posted by madhivanan

Note that ISDATE is not reliable like ISNUMERIC

Select isdate(2005),isdate('2006')

So, if all date values are stored in YYYYMMDD format, you need to check it's length also

Select Cast(Case when len(ODRDT)=8 and IsDate(ODRDT) = 1 then ODRDT else Null end as Datetime) from Table1

Madhivanan

Heh... thanks... didn't know that one.

--Jeff Moden

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-06-04 : 13:29:23

Thanks Madhivanan !!..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources