Date

Question

Post reply

Date

Mike Levan

SSChampion

Points: 14201
More actions
October 26, 2007 at 11:38 am

#62182

How can I check if a date is a valid date format, the field has date in '11/16/2004' format.
overall i need to check if the date is in the right format

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

....

declare @myanswer bit

select @myanswer=isdate('11/24/1998')

....

isdate returns 1 if valid 0 otherwise (valid also means in a range SQL can store).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mike Levan SSChampion Points: 14201 More actions · Answer 2

its not the exact date i need to check rather i want to check the format

Antares686 SSC Guru Points: 125444 More actions · Answer 3

What do you mean. IsDate on a value will return 1 is the value (plus formatting) is acceptable as a date and 0 if not. But if you are talking about data in a datetime field the answer is the format is valid but depends on whom you are trying to display.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

IsDate checks that the format of whatever you have in quotes would convert to a date. So - the format has to be right, AND it would have to evaluate to a "real date".

Pass it a string parameter.

If you're storing the data IN a datetime field - it HAS to be valid.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 5

SELECT importdate FROM

WHERE

CAST(substring(importdate,8,4) AS integer) < 1754

OR

CAST(substring(importdate,8,4) AS integer) > 9998

if u r checking whether ur date column is between the datetime range

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 6

Mike is wanting to check the formatting of the date, not necessarily whether its a date or not.

Mike Levan (10/26/2007)
its not the exact date i need to check rather i want to check the format

You'll need to use string functions to check for the specific format if the field is in fact a char/varchar field.

CREATE TABLE #temp (dateString VARCHAR(50))

INSERT #temp

SELECT '4/1/2007'

UNION

SELECT 'Feb 1, 2006'

UNION

SELECT '05/08/2012'

UNION

SELECT '05/21/12'

SELECT

datestring

,ISDATE(datestring) as IsDate

,CASE WHEN SUBSTRING(datestring,2,1) = '/' THEN 'yes' ELSE 'no' END as isValidFormat

FROM

#temp

DROP TABLE #temp

Gives you

datestringIsDateisValidFormat

05/08/20121no

05/21/121no

4/1/20071yes

Feb 1, 20061no

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Greg Snidow SSCoach Points: 16108 More actions · Answer 7

Mike Levan (10/26/2007)
How can I check if a date is a valid date format, the field has date in '11/16/2004' format.
overall i need to check if the date is in the right format

You might have other issues as well. Do all of your users enter dates the same? there is really only one way to interpret '11/16/2004', but if it is '6/8/2004' it could be June 8, or it could be Aug 6. When users are allowed to enter dates in char field you will get inconsistency. You should try to verify which it is.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

russell-154600 Ten Centuries Points: 1375 More actions · Answer 8

the real problem is that you are storing dates in character data types. else all values are already in the same format. should store dates in smalldatetime or datetime fields.

since u are storing your dates in character fields, Jason is right, you'll need to use string manipulation as he demonstrated.

Mike Levan SSChampion Points: 14201 More actions · Answer 9

13/34/2007 is not a valid date, need to check like this.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 10

mrpolecat

SSCertifiable

Points: 7014

October 26, 2007 at 2:06 pm

#745908

isdate('13/34/2007') will return false

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 11

Mike Levan (10/26/2007)
13/34/2007 is not a valid date, need to check like this.

Mike,

As we've noted here, there is a difference in checking to see if "13/34/2004" is a valid date or a valid date format. There is a difference. :w00t:

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 12

I agree with Jason... I think ISDATE, by itself, is a very bad way to check for valid dates. For example, the following all return "1" using ISDATE...

[font="Courier New"]SELECT ISDATE('2007')

SELECT ISDATE('JAN 2007')[/font]

At least add in a check for length and a check for slashes in the right spots...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 13

Jason certainly had the right idea... and this goes just a bit further for forcing a format... but it's not 100% bullet proof...

DECLARE @test-2 TABLE (SomeDate VARCHAR(20))

INSERT INTO @test-2

(SomeDate)

SELECT '2007' UNION ALL

SELECT '01/01/1753' UNION ALL

SELECT '1753' UNION ALL

SELECT '1752' UNION ALL

SELECT 'JAN 2007' UNION ALL

SELECT '11/34/2007' UNION ALL

SELECT '34/11/2007' UNION ALL

SELECT '12/28/2007' UNION ALL

SELECT '28/12/2007' UNION ALL

SELECT '1/28/2007' UNION ALL

SELECT '28/1/2007' UNION ALL

SELECT '05/08/2012' UNION ALL

SELECT '05/21/12' UNION ALL

SELECT '4/1/2007' UNION ALL

SELECT 'Feb 1, 2006' UNION ALL

SELECT 'Feb 1 2006'

SELECT SomeDate,

ISDATE(SomeDate) AS IsDate,

CASE

WHEN ISDATE(SomeDate) = 1

AND SomeDate LIKE '[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]'

THEN 1

ELSE 0

END AS IsValidDateAndFormat

FROM @test-2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)