• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1255
  • Last Modified:

SQL Stored Procedure to validate date

I am looking to design a SQL Stored Procedure (SQL Server 2000) that will validate a date.  Now, before you think this is a normal date, here is a more specific definition of what I am looking to validate.

The field in the DB is a varchar field (10 chars I think) and the date format is:  YYYYMMDD (i.e. 20071015)

It might sound a little whacked (and it is) but this is what I have to work with.

Of course, the SQL SP would have a parameter that would need to be passed to it, that being the date string as outlined above.

TIA for any help!
0
dstjohnjr
Asked:
dstjohnjr
  • 5
  • 2
2 Solutions
 
YveauCommented:
What you are looking for is the buildin SQL function IsDate() !

returns 1 if the argument is a valid date, 0 otherwise.

Hope this helps ...
0
 
YveauCommented:
Example:

select isdate('20071015')
-->> result:

-----------
1

select isdate('20070231')
-->> result:

-----------
0


select isdate('BunchOfText')
-->> result:

-----------
0

Hope this helps ...
0
 
nmcdermaidCommented:
Be aware, isdate will also return 1 for:

2007-10-15

10/15/2007

If you want to strictly conform to YYYYMMDD you'll need something more complicated like

SELECT * FROM Table
WHERE Field LIKE '20[0-9][0-9][01][0-9][0-3][0-9]'
AND ISDATE(Field) = 1



0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
YveauCommented:
... but that will return 1 to '20071939' as well ... and that really is not a valid date.

Hope this helps ...
0
 
YveauCommented:
... you could combine the two:
... oops that is what mmcdermaid did
... sorry, too early I guess :-)

The '20071939' thing is not true as I said before !
0
 
nmcdermaidCommented:
I didn't check if ISDATE('20071939') = 0 or 1.... maybe the questioner can tell us :)
0
 
YveauCommented:
It's a 0, so your AND clause filters these kinds of values.
That's the part I missed ...
0
 
dstjohnjrAuthor Commented:
Thanks for the assistance experts and sorry for the delay.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now