Find that column value is in date Formate or not

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)

Find that column value is in date Formate or not

Author

Topic

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-12-25 : 03:53:08

Hi

I have a table, name is history and column is Hdate(its DataType is Varchar(30)). Date stored into Hdate column like this

Hdate
-------------------
20070102
20070103
NULL
20070104
aaaaaa
20070105
''
20070106
---------------------
I want output like below

Hdate
-------------------
20070102
20070103
20070104
20070105
20070106
---------------------

means i want that value which can be converted into "yyyy-mm-dd" formate.

Ranjeet Kumar Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-25 : 03:59:35

SELECT Hdate
FROM Table
WHERE ISDATE(Hdate)=1

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-12-25 : 04:09:21

Thanks..................

Ranjeet Kumar Singh

georgev
Posting Yak Master

122 Posts

Posted - 2007-12-25 : 17:51:23

Oh, and convert HDate to the proper datatype :)
[CODE]
UPDATE myTable
SET hDate = NULL
WHERE NOT IsDate(hDate)
GO

ALTER TABLE myTable
ALTER COLUMN hDate datetime
GO
[/CODE]

George
<3Engaged!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-26 : 00:57:36

quote:
Originally posted by visakh16

SELECT Hdate
FROM Table
WHERE ISDATE(Hdate)=1

Handle ISDATE() with care
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-26 : 01:01:13

quote:
Originally posted by georgev

Oh, and convert HDate to the proper datatype :)
[CODE]
UPDATE myTable
SET hDate = NULL
WHERE NOT IsDate(hDate)
GO

ALTER TABLE myTable
ALTER COLUMN hDate datetime
GO
[/CODE]

George
<3Engaged!

This is what usually I suggest

"Use proper DATETIME datatype to store dates"

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-26 : 21:58:42

quote:
Originally posted by visakh16

SELECT Hdate
FROM Table
WHERE ISDATE(Hdate)=1

Heh... ISDATE is as fickle as ISNUMBER is... for example, the following returns a "1"...

SELECT ISDATE('2006')

If you're going to do this right, you need to check for number of digits as well.

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-27 : 02:57:44

quote:
Originally posted by Jeff Moden

quote:
Originally posted by visakh16

SELECT Hdate
FROM Table
WHERE ISDATE(Hdate)=1

Heh... ISDATE is as fickle as ISNUMBER is... for example, the following returns a "1"...

SELECT ISDATE('2006')

If you're going to do this right, you need to check for number of digits as well.

--Jeff Moden

Yes it is. Thats why I blogged it here
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

georgev
Posting Yak Master

122 Posts

Posted - 2007-12-29 : 19:45:22

The reason IsDate() returns 1 for '2006' is because
[CODE]
SELECT Convert(datetime, '2006')
[/CODE]
Returns a datetime!

George
<3Engaged!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-31 : 04:55:31

quote:
Originally posted by georgev

The reason IsDate() returns 1 for '2006' is because
[CODE]
SELECT Convert(datetime, '2006')
[/CODE]
Returns a datetime!

George
<3Engaged!

Yes. Thats why you should be careful when using ISDATE() function

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources