[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Stored Procedure to validate date

Posted on 2007-10-15
8
Medium Priority
?
1,218 Views
Last Modified: 2011-09-20
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
Comment
Question by:dstjohnjr
  • 5
  • 2
8 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20081298
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
 
LVL 18

Accepted Solution

by:
Yveau earned 1000 total points
ID: 20081315
Example:

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

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

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

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


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

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

Hope this helps ...
0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 1000 total points
ID: 20082385
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Yveau
ID: 20083301
... but that will return 1 to '20071939' as well ... and that really is not a valid date.

Hope this helps ...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20083304
... 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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20089927
I didn't check if ISDATE('20071939') = 0 or 1.... maybe the questioner can tell us :)
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20089955
It's a 0, so your AND clause filters these kinds of values.
That's the part I missed ...
0
 

Author Closing Comment

by:dstjohnjr
ID: 31408176
Thanks for the assistance experts and sorry for the delay.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question