Date Format Query in a Text Field

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)

Date Format Query in a Text Field

Author

Topic

kstoimenov
Starting Member

2 Posts

Posted - 2010-03-08 : 12:56:48

Hi,

I have a table where I have a field/column that represents Dates. The problem is that we haven't had a validation for this field and it's a text format not - date format. I am trying to find all the records with a date format 'mm/dd/yyyy' and exclude all the records with a different format. Is there a way to do it in a text field?I hope this make sense.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 13:11:17

try using convert

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2010-03-08 : 13:18:43

You don't have to limit yourself to just 'mm/dd/yyyy' format if you don't have to - SQL Server can distinguish other formats as well. For example:


declare @t table (date text)

insert @t
select '01/01/2010' union
select '2/02/2010' union
select '4/6/10' union
select 'june 5 2010' union
select 'jan 5 09'

select convert(datetime, convert(varchar, date)) from @t

Some days you're the dog, and some days you're the fire hydrant.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-08 : 14:01:41

If you are not worry about performance, this may work for you.

select * from tableName where isdate(columnName) > 0 and charIndex('/', columnName) = 3 and charIndex('/', columnName, 4) = 6 and len(columnName) = 10

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-03-08 : 14:33:21

[code]select
MyDate,
[IsValidDate MM/DD/YYYY] =
case
when MyDate not like '[0-1][0-9]/[0-3][0-9]/[1-9][0-9][0-9][0-9]'
then 0
when isdate(substring(MyDate,7,4)+substring(MyDate,1,2)+substring(MyDate,4,2)) <> 1
then 0
else 1
end
from
( -- Test Data
select MyDate ='12-31-1999' union all
select MyDate ='1231/1999' union all
select MyDate ='12/31/1999' union all
select MyDate ='13/31/1999' union all
select MyDate ='02/29/1999' union all
select MyDate ='02/29/2000' union all
select MyDate ='02/29/1900' union all
select MyDate ='12/31/1752'
) a[/code]
Results:
[code]MyDate IsValidDate MM/DD/YYYY
---------- ----------------------
12-31-1999 0
1231/1999 0
12/31/1999 1
13/31/1999 0
02/29/1999 0
02/29/2000 1
02/29/1900 0
12/31/1752 0

(8 row(s) affected)[/code]

CODO ERGO SUM

kstoimenov
Starting Member

2 Posts

Posted - 2010-03-08 : 15:06:01

I tested only Michael's query and worked perfectly for me.

Thanks for the fast respond..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources