IsDate not functioning as expected

Question

IsDate not functioning as expected

Sarah Peterson-400784

SSC Enthusiast

Points: 117
More actions
February 22, 2007 at 12:27 pm

#115720

I have created two SQL views, used for importing data via DTS packages. Both views contain a date field called accrued_thru_dt. These views are not new and have been used several different times but I have not run into this issue previously.
Query 1 (shortened for simplicity)--the problem child
select accrued_thru_dt,
CASE
   WHEN ISDATE(CONVERT(VARCHAR(10), Accrued_Thru_Dt, 112)) = 0 THEN NULL
   ELSE CAST(CONVERT(VARCHAR(10),Accrued_Thru_Dt, 112) AS DATETIME)
END AS Accrued_Thru_Dt from pcs_bais_bank
Query 1 Results:
4/25/2006                                         NULL
8/27/2006                                         NULL
6/21/2006                                         NULL
11/15/2006                                        2006-11-15 00:00:00.000
8/27/2006                                         NULL
6/06/2006                                         NULL
7/31/2006                                         NULL
Seems odd to me that the only records that do not return an IsDate value of 1 are those records that have a 2-digit month value...

Query 2 (Shortened for simplicity):
select accrued_thru_dt,
CASE
   WHEN ISDATE(CONVERT(VARCHAR(10), Accrued_Thru_Dt, 112)) = 0 THEN NULL
   ELSE CAST(CONVERT(VARCHAR(10),Accrued_Thru_Dt, 112) AS DATETIME)
END AS Accrued_Thru_Dt from pcs_vision_bank

Query 2 Results:
07/16/2006                                        2006-07-16 00:00:00.000
08/06/2006                                        2006-08-06 00:00:00.000
06/20/2006                                        2006-06-20 00:00:00.000
06/25/2006                                        2006-06-25 00:00:00.000
06/18/2006                                        2006-06-18 00:00:00.000
08/15/2006                                        2006-08-15 00:00:00.000
08/14/2006                                        2006-08-14 00:00:00.000
04/16/2006                                        2006-04-16 00:00:00.000
So, what am I missing here? Shouldn't IsDate recognize a single digit month as well? Any help or assistance to this matter would be greatly appreciated as I have now hit a time crunch and will be entering 'panic-mode' soon!
Thanks so much....

Viewing 3 posts - 1 through 2 (of 2 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

ISDATE does recognize single digit months. Your descriptiong states that your accrued_thru_dt column is a datetime. I cannot reproduce your problem. Can you come up with a t-sql example to prove what you are seeing?

DECLARE @pcs_bais_bank TABLE (accrued_thru_dt datetime)

INSERT INTO @pcs_bais_bank

SELECT '4/25/2006' UNION ALL

SELECT '8/27/2006' UNION ALL

SELECT '6/21/2006' UNION ALL

SELECT '11/15/2006' UNION ALL

SELECT '8/27/2006' UNION ALL

SELECT '6/06/2006' UNION ALL

SELECT '7/31/2006'

select accrued_thru_dt,

CASE

WHEN ISDATE(CONVERT(VARCHAR(10), Accrued_Thru_Dt, 112)) = 0 THEN NULL

ELSE CAST(CONVERT(VARCHAR(10),Accrued_Thru_Dt, 112) AS DATETIME)

END AS Accrued_Thru_Dt

from @pcs_bais_bank

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

I can reproduce your problem by setting the some environmental options by running either:

set dateformat dmy

set language British

You seem to be expecting this option to be mdy.

Run DBCC USEROPTIONS to see specification of the connections options

SQL = Scarcely Qualifies as a Language