Help with Date part

Question

Post reply

Help with Date part

Viewing 13 posts - 16 through 27 (of 27 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

sql.kishore1 (5/17/2010)

Thanks Guys. I got it now.

Cool... two way street here, though... would you mind posting your final code so other folks can learn from it? Thanks.

--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)
Intro to Tally Tables and Functions

sql.kishore1 SSC-Addicted Points: 435 More actions · Answer 2

sql.kishore1

SSC-Addicted

Points: 435

May 18, 2010 at 10:03 am

#1168920

Rob Schripsema solution Solves my ISSUE.

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 3

Jeff Moden (5/17/2010)

Heh... try the following, Rob...

SELECT ISDATE('2000')You sure you want that as a valid date?

I had a look at the ISDATE() function ( http://msdn.microsoft.com/en-us/library/ms187347.aspx ) and saw the example in the first comment; specifically ISDATE('2009 3 5 .-') -- it returns 1 in 2005 too.

I do not think ISDATE() is a good way to solve the problem OP has. Also, given the anomalies, I was wondering when would you use ISDATE() and be sure that it gives you correct expected result.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 4

I do not think ISDATE() is a good way to solve the problem OP has. Also, given the anomalies, I was wondering when would you use ISDATE() and be sure that it gives you correct expected result.

You (and Jeff) are right that IsDate() isn't very reliable when you're working with a wide variety of input strings. Thanks to the feedback here, I've also done some follow up reading and learned just how unreliable it is.

BUT --- the OP gave an example where the so-called date was in a nn/nn/nnnn format, and the problem was just that one (or potentially more) of the month/day/year values fell outside of the acceptable range for a datetime. I'd argue that if OP could run a few simple queries to check that each value in that column was, in fact, 10 chars long, and had slashes in the appropriate positions, then IsDate() would let the query run without crashing. That's how I understood the original question, at least.

Wouldn't you agree that it is useful within that scenario?

Rob Schripsema
Propack, Inc.

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 5

Rob Schripsema (5/18/2010)

BUT --- the OP gave an example where the so-called date was in a nn/nn/nnnn format, and the problem was just that one (or potentially more) of the month/day/year values fell outside of the acceptable range for a datetime. I'd argue that if OP could run a few simple queries to check that each value in that column was, in fact, 10 chars long, and had slashes in the appropriate positions, then IsDate() would let the query run without crashing. That's how I understood the original question, at least.

Wouldn't you agree that it is useful within that scenario?

I agree. I think I worded my opinion wrong. I was trying to take the view that given a "I do not know what all is there in the supposed date field" scenario; @john-2 Rowan's solution seems more sturdy. Thanks to you (and Jeff's example) that I went through the IsDate() function and realized that it is not as sturdy as it sounds. 🙂

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

sql.kishore1 (5/18/2010)

Rob Schripsema solution Solves my ISSUE.

Rob's code is technically correct for what it was intended to do but you need to make real sure that you don't have entries like '2005' or "Apr 2005" because although they are certainly convertable to dates (which is why ISDATE() lets them through), they aren't valid dates according to what you want to do. You need to do some extra verification like checking to make sure that ther are two slaches for starters.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

rjv_rnjn (5/18/2010)

Rob Schripsema (5/18/2010)

BUT --- the OP gave an example where the so-called date was in a nn/nn/nnnn format, and the problem was just that one (or potentially more) of the month/day/year values fell outside of the acceptable range for a datetime. I'd argue that if OP could run a few simple queries to check that each value in that column was, in fact, 10 chars long, and had slashes in the appropriate positions, then IsDate() would let the query run without crashing. That's how I understood the original question, at least.

Wouldn't you agree that it is useful within that scenario?

I agree. I think I worded my opinion wrong. I was trying to take the view that given a "I do not know what all is there in the supposed date field" scenario; @john-2 Rowan's solution seems more sturdy. Thanks to you (and Jeff's example) that I went through the IsDate() function and realized that it is not as sturdy as it sounds. 🙂

Actually, ISDATE() is very sturdy. It wasn't designed to validate date formats... it was designed to determine if a string value COULD be converted to a DATE. It's much like ISNUMERIC. People frequently mistake ISNUMERIC as an ISALLDIGITS function and it was never designed for that. Neither was ISDATE().

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Rob Schripsema (5/18/2010)

I do not think ISDATE() is a good way to solve the problem OP has. Also, given the anomalies, I was wondering when would you use ISDATE() and be sure that it gives you correct expected result.

You (and Jeff) are right that IsDate() isn't very reliable when you're working with a wide variety of input strings. Thanks to the feedback here, I've also done some follow up reading and learned just how unreliable it is.

BUT --- the OP gave an example where the so-called date was in a nn/nn/nnnn format, and the problem was just that one (or potentially more) of the month/day/year values fell outside of the acceptable range for a datetime. I'd argue that if OP could run a few simple queries to check that each value in that column was, in fact, 10 chars long, and had slashes in the appropriate positions, then IsDate() would let the query run without crashing. That's how I understood the original question, at least.

Wouldn't you agree that it is useful within that scenario?

I agree... if the expected format for dates is, in fact, limited to the mm/dd/yyyy format, and is guaranteed to always be so, then ISDATE() by itself will probably do.

Being the distrusting person I am, a tweak to the code would do nicley... it's not perfect, but it'll do a lot better than ISDATE() alone...

SELECT whatever



   FROM dbo.yourtable

  WHERE whatever

    AND ISDATE(Date) = 1

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

This ISDATE() is still important here to trap for illegal dates like 02/29/2007 and other things that the simple pattern matching will miss...

--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)
Intro to Tally Tables and Functions

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 9

Rob Schripsema

SSCertifiable

Points: 7469

May 18, 2010 at 6:46 pm

#1169182

Nice tweak, Jeff. Not perfect -- won't allow for years prior to 2000 (and I, for one, have a birthday before 2000 :hehe:) -- but a definite improvement.

Rob Schripsema
Propack, Inc.

WayneS SSC Guru Points: 95405 More actions · Answer 10

Rob Schripsema (5/18/2010)

Nice tweak, Jeff. Not perfect -- won't allow for years prior to 2000 (and I, for one, have a birthday before 2000 :hehe:) -- but a definite improvement.

Perfectly adequate for FUTURE dates.

To make it work for dates in the past century:

SELECT whatever



   FROM dbo.yourtable

  WHERE whatever

    AND ISDATE(Date) = 1

AND Date LIKE '[0-1][0-9]/[0-3][0-9]/[12][09][0-9][0-9]'

Of course, this will allow dates in 2999 now...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 11

Jeff Moden (5/18/2010)

Actually, ISDATE() is very sturdy. It wasn't designed to validate date formats... it was designed to determine if a string value COULD be converted to a DATE. It's much like ISNUMERIC. People frequently mistake ISNUMERIC as an ISALLDIGITS function and it was never designed for that. Neither was ISDATE().

But wasn't that the point of the post towards the bottom of the page. That in 2008 (I don't have 2008 so can't validate that) CAST('2009 3 5 .-' AS DATE) fails but ISDATE('2009 3 5 .-') returns 1.

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

rjv_rnjn (5/19/2010)

Jeff Moden (5/18/2010)

Actually, ISDATE() is very sturdy. It wasn't designed to validate date formats... it was designed to determine if a string value COULD be converted to a DATE. It's much like ISNUMERIC. People frequently mistake ISNUMERIC as an ISALLDIGITS function and it was never designed for that. Neither was ISDATE().

But wasn't that the point of the post towards the bottom of the page. That in 2008 (I don't have 2008 so can't validate that) CAST('2009 3 5 .-' AS DATE) fails but ISDATE('2009 3 5 .-') returns 1.

I don't have 2k8 either but does it work for the DATETIME datatype? If so, ISDATE is vindicated.

--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)
Intro to Tally Tables and Functions

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 13

rjv_rnjn

Hall of Fame

Points: 3023

May 20, 2010 at 7:41 am

#1170127

Jeff Moden (5/19/2010)

I don't have 2k8 either but does it work for the DATETIME datatype? If so, ISDATE is vindicated.

🙂

Yes, I tested it for DATETIME and it works.