check the date format - 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)

check the date format

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-18 : 10:54:09

How I do have to check if the date value passed is in 'yyyymmdd' format.

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-18 : 11:33:46

ISDATE()

??

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-18 : 11:55:02

Isdate will pass the date even in this '200722'. In this eg. the month and day are not appended by 0's. I have to make sure there are 8 characters in the date.
so can i use isdate() function first and then check if the date length is equal to exactly 8 characters???

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-18 : 12:02:13

[code]
select
DT,
Check_Result =
case
when DT is null
then 'Cannot be null'
when DT like '%[^0-9]%'
then 'Must be numeric'
when len(DT) <> 8
then 'Must be 8 digits'
when convert(int,substring(DT,1,4)) < 1753
then 'Year is invalid'
when convert(int,substring(DT,5,2)) not between 1 and 12
then 'Month is invalid'
when isdate(DT) <> 1
then 'Date is invalid'
else 'Valid Date'
end
from
(
Select DT = null union all
Select DT = '179301o1' union all
Select DT = '199901001' union all
Select DT = '1999011' union all
Select DT = '17520101' union all
Select DT = '17530101' union all
Select DT = '19992101' union all
Select DT = '20030229' union all
Select DT = '20040229'
) a

DT Check_Result
--------- ----------------
NULL Cannot be null
179301o1 Must be numeric
199901001 Must be 8 digits
1999011 Must be 8 digits
17520101 Year is invalid
17530101 Valid Date
19992101 Month is invalid
20030229 Date is invalid
20040229 Valid Date

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

CODO ERGO SUM

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-18 : 12:25:25

Thanks michael...

How do i check if the date in 'yyyymmdd' format is greater than today's date or not???

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-18 : 12:33:26

Where is this date coming from? From your description, it sounds like a parameter; if it is, it should be declared with the proper data type -- DateTime. And it is the client's job that calls the stored proc to supply a valid date. You should only need to parse dates like this in T-SQL if you are importing data from another system or table that is storing things as strings.

- Jeff
http://weblogs.sqlteam.com/JeffS

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-18 : 12:37:47

my date parameter is stored as varchar in the database. So i have to do validation on this before i send it over to the client.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-18 : 13:33:07