Date validation - 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

Date validation

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-03-18 : 09:55:02

Hi iwant to check a date it must be in CCYYMMDD format if it is notin that format i must update a column.Isdate function will check only for the given value is date or not but to heck it it is of the format which we need how to do it

susan

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-03-18 : 09:56:36

also i need to check another column which has the SSN number in the format {xxx-xx-xxxx}.Here the x denotes the digits first phrase must have 3 digits followed by 2 digits followed by 4 digits how to check for it also

susan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 10:22:01

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121882

E 12°55'05.63"
N 56°04'39.26"

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-18 : 11:02:08

declare @a table (id int,ssn varchar(100))

insert into @a values(1,'123-r3-12f4')
insert into @a values(2,'12323-1234')
insert into @a values(3,'123-2-1234')
insert into @a values(4,'123-23-14')
insert into @a values(5,'3-231234')

select * from @a where ssn like'[a-z,0-9][a-z,0-9][a-z,0-9]-[a-z,0-9][a-z,0-9]-[a-z,0-9][a-z,0-9][a-z,0-9][a-z,0-9]'

Karthik

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-03-19 : 00:31:02

Hi Peso the date validation is not working for YYYYMMDD format

susan

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-03-19 : 00:32:06

Thanks karthik ur idea of ssn works quite fine can u tell me some idea for date validatio to check if it is in YYYYMMDD format or not in backend

susan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-19 : 00:58:17

[code]
select
test_date
from
( -- test data
select test_date = '20091231'
union all
select test_date = '2009/12/31'
union all
select test_date = '2009-12-31'
union all
select test_date = '20091232'
union all
select test_date = 'Dec 23, 2000'
union all
select test_date = null
) a
where
case
when test_date is null then 0
when isdate(test_date) <> 1 then 0
when test_date like '%[^0-9]%' then 0
else 1
end = 1
[/code]

Results:
[code]
test_date
------------
20091231

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

CODO ERGO SUM

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-03-19 : 01:20:51

Thank you michael it works

susan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-19 : 03:58:01

quote:
Originally posted by susan_151615

Hi Peso the date validation is not working for YYYYMMDD format

Of course it does!
It gives exactly same result as MVJ's suggestion.
But MVJ's suggestion will fail if you include this test data

    union all
    select test_date = '2010'

where my suggestion will filter that data out.

Did you even give it a try?

SELECT	*
FROM	( -- test data
    select test_date = '20091231'
    union all
    select test_date = '2009/12/31'
    union all
    select test_date = '2009-12-31'
    union all
    select test_date = '2010'
    union all
    select test_date = '20091232'
    union all
    select test_date = 'Dec 23, 2000'
    union all
    select test_date = null
    ) a
where test_date LIKE '[12][0-9][0-9][0-9][01][0-9][0-3][0-9]'
AND ISDATE(test_date) = 1

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources