checking if string is a date - 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 2008 Forums

Transact-SQL (2008)

checking if string is a date

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-09-14 : 03:22:48

I get some crappy data in csv files which 'should' contain dates, this comes over in the format:-

d/mm/yyyy (1/12/2009)

d/m/yyyy (1/1/2009)

dd/m/yyyy (31/1/2009

d/m/yy (1/1/09)

dd/m/yy (31/1/09)

Anyone got a way of checking and converting these into proper dates in the format 'dd/mm/yyyy'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-14 : 03:47:10

select
*,
right('00'+parsename(replace(crappy,'/','.'),3),2) + '/' +
right('00'+parsename(replace(crappy,'/','.'),2),2) + '/' +
right('20'+parsename(replace(crappy,'/','.'),1),4)
from
(
select '1/12/2009' as crappy union all
select '1/1/2009' as crappy union all
select '31/1/2009' as crappy union all
select '1/1/09' as crappy union all
select '31/1/09' as crappy
)crappy_data

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 03:56:12

Convert them to DATETIME datatype?

Use

SET DATEFORMAT dmy

so SQL doesn't ambiguously parse them as being M-D-Y or even Y-M-D

!

We pull all CSV type data into staging tables with an extra column or two for Error No / Error Message then do things like:


SET DATEFORMAT dmy
UPDATE MyStagingTable
SET ErrNo = 1, ErrMsg = 'MyDateColumn invalid'
WHERE IsDate(MyDateColumn) <> 1
      OR MyDateColumn NOT LIKE '%/%/%'
      OR MyDateColumn LIKE '%[^0-9/]%'
      OR ... any tests you can come up with ...

INSERT INTO MyRealTable (Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM MyStagingTable
WHERE COALESCE(ErrNo, 0) = 0

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-09-14 : 04:02:41

Thanks both.

Sachin.Nand

2937 Posts

Posted - 2010-09-14 : 04:49:18

Wont IsDate function do the job?


set dateformat dmy
select ISDATE('31/12/2009')

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 05:44:42

"Wont IsDate function do the job?"

Maybe not. If you have


UPDATE MyTable
SET MyDateColumn = CASE WHEN ISDATE(MyStringColumn) THEN CONVERT(datetime, MyStringColumn) ELSE NULL END

SQL may optimise to do the CONVERT anyway. I've probably used a rubbish example that will actually work, but I've had to do it as a two-step process to avoid this in the past, and I have always had to use SET DATEFORMAT to provide a hint to IsDate as to what style is acceptable ... otherwise it will allow all sorts of rubbish (valid looking dates, but not in an acceptable format within the constraints of my Spec!)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-14 : 05:48:48

quote:
Originally posted by Idera

Wont IsDate function do the job?

set dateformat dmy
select ISDATE('31/12/2009')

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

select ISDATE('2009')
select ISDATE(3000)
select ISDATE(76000/43)

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-09-14 : 05:59:33

quote:
Originally posted by madhivanan

quote:
Originally posted by Idera

Wont IsDate function do the job?

set dateformat dmy
select ISDATE('31/12/2009')

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

select ISDATE('2009')
select ISDATE(3000)
select ISDATE(76000/43)

Madhivanan

Failing to plan is Planning to fail

I think the reason IsDate works in the above scenarios is because SQL server supports implicit varchar & integer conversion to datetime.

select convert(datetime,'2009')
select convert(datetime,3000)
select convert(datetime,76000/43)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 06:10:54

"I think the reason IsDate works in the above scenarios is because SQL server supports implicit varchar & integer conversion to datetime"

Indeed, but the key point (for me) is that they may not be in the format that you are expecting in your data feed, in which case they are errors (even if capable of being converted to a Date by SQL's ambiguous date parser routine!)

Sachin.Nand

2937 Posts

Posted - 2010-09-14 : 06:23:37

Indeed, but the key point (for me) is that they may not be in the format that you are expecting in your data feed...

Then it becomes totally illogical to get the job done by SQL which is supposed to be done by Business Logic Layer.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-14 : 06:30:43

quote:
Originally posted by Idera

quote:
Originally posted by madhivanan

quote:
Originally posted by Idera

Wont IsDate function do the job?

set dateformat dmy
select ISDATE('31/12/2009')

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

select ISDATE('2009')
select ISDATE(3000)
select ISDATE(76000/43)

Madhivanan

Failing to plan is Planning to fail

I think the reason IsDate works in the above scenarios is because SQL server supports implicit varchar & integer conversion to datetime.

select convert(datetime,'2009')
select convert(datetime,3000)
select convert(datetime,76000/43)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

My point is handle isdate() with care
http://beyondrelational.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 07:24:12

quote:
Originally posted by Idera

Indeed, but the key point (for me) is that they may not be in the format that you are expecting in your data feed...

Then it becomes totally illogical to get the job done by SQL which is supposed to be done by Business Logic Layer.

Yes you could do it in another layer. Many places do this in SQL for bulk import though. We do ... and we also do this for data integration which other databases where data is arriving structured, natively formatted, and "perfect" Ha!Ha! - it still requires data cleanup.

For example, data coming in from Oracle yesterday had duplicate records on the PK which turned out to be a trailing space that Oracle had been happy to store and the User had managed to create two identical sets of records - presumably having entered the first one with a trailing space they could then never find them again!

So another test, this time for "are there any duplicates on primary key", added to our data integration suite.

We have "is this a valid date" in plenty of places where it is, allegedly, impossible for it to be anything other than that - but in the not too distant future "number of seconds since 1970" is going to fall over and its nice to catch things like that before they wreck the whole database ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources