Identififying invalid dates - 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 2000 Forums

Transact-SQL (2000)

Identififying invalid dates

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-10-19 : 14:28:52

Hi,

I have a date field in my table which I want to change from a varchar to a datetime datatype. I need to identify any invalid dates in there before I do the conversion as I keep getting an error. Is there an easy way to identify invalid date types?

Cheers

Paul

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-19 : 14:30:12

SELECT * FROM myTable WHERE IsDate(DateCol)=0

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-10-19 : 14:51:22

Hi Robvolk,

I just tried your query, but it returns dates which are OK e.g 19/06/1979, 15/05/1962 etc
Cheers

Paul

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-19 : 15:27:33

Probably your system is set to american format mm/dd/yyyy
try set dateformat dmy first.

You will also need to specify a style when you convert the dates or make sure the dateformat is set.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-10-19 : 15:39:51

Hi,

I just checked my OS regional settings and they are all UK. Is there something within sql server I need to change?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-20 : 01:20:51

Go with what Nigel suggested:

SET DATEFORMAT dmy

This tells SQL Server to use dates in the UK format (day/month/year) rather than the default US format (mdy)

Owais

Make it idiot proof and someone will make a better idiot

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-20 : 01:31:19

I always try to make systems date format independent.
Any time you pass a date in character format specify it as yyyymmdd.
Always format the date for output - don't rely on the default - that means specifying a style in the SP if you are not relying on client settings or passing it as datetime or yyyymmdd and converting on the client if you are.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources