Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

problem using ISDATE in a Function Expand / Collapse
Author
Message
Posted Friday, September 30, 2011 10:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:35 AM
Points: 1,172, Visits: 2,413
wolfkillj (9/30/2011)
I certainly sympathize with you for having to work with suboptimal requirements with no ability to change them!

Do you really need the hyphen separators when you convert @inDDMMYYYYHHMMSS to @TextDate? If you leave them out, @TextDate will be in ISO 8601 basic format (YYYYDDMM, e.g., '20110930'). ISDATE() works correctly on character strings in ISO 8601 basic format (with or without time data) regardless of the DATEFORMAT setting.



Actually, you could just go with full ISO 8601 format, thusly: '2011-09-01T13:37:44'

From BOL:

The advantage in using the ISO 8601 format is that it is an international standard. Date and time values that are specified by using this format are unambiguous. This format is not affected by the SET DATEFORMAT, SET LANGUAGE, of login default language settings.


http://msdn.microsoft.com/en-us/library/ms180878.aspx#ISO8601Format


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1183837
Posted Friday, September 30, 2011 10:57 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 3,081, Visits: 11,230
Here is a fairly simple check that will work with any setting of DATEFIRST:
select
a.dt,
IsValidDateTime =
case
-- Check that date string is in correct format, mm/dd/yyyy hh:mm:ss
when dt not like '[0-1][0-9]/[0-3][0-9]/[1-9][0-9][0-9][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9]'
then 0
-- Reformat and test date in universal format of yyyymmdd hh:mm:ss
when isdate(substring(a.dt,7,4)+substring(a.dt,1,2)+substring(a.dt,4,2)+substring(a.dt,11,9)) <> 1
then 0
else 1
end
from
( -- Test Data
select dt = '08/31/2001 14:34:56' union all
select dt = '08/31/2001 34:34:56' union all
select dt = '08/31/2001 14:64:56' union all
select dt = '08/31/2001 14:34:66' union all
select dt = '08/31/2001 25:34:56' union all
select dt = '08/31/2001 14:34:56' union all
select dt = '13/01/2001 14:34:56' union all
select dt = '0a/31/2001 14:34:56' union all
select dt = '08/32/2001 14:34:56' union all
select dt = '21/31/2001 14:34:56'
) a

Results:
dt                  IsValidDateTime 
------------------- ---------------
08/31/2001 14:34:56 1
08/31/2001 34:34:56 0
08/31/2001 14:64:56 0
08/31/2001 14:34:66 0
08/31/2001 25:34:56 0
08/31/2001 14:34:56 1
13/01/2001 14:34:56 0
0a/31/2001 14:34:56 0
08/32/2001 14:34:56 0
21/31/2001 14:34:56 0

(10 row(s) affected)





Post #1183843
Posted Friday, September 30, 2011 11:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 2,404, Visits: 7,311
Michael Valentine Jones (9/30/2011)
Here is a fairly simple check that will work with any setting of DATEFIRST:
select
a.dt,
IsValidDateTime =
case
-- Check that date string is in correct format, mm/dd/yyyy hh:mm:ss
when dt not like '[0-1][0-9]/[0-3][0-9]/[1-9][0-9][0-9][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9]'
then 0
-- Reformat and test date in universal format of yyyymmdd hh:mm:ss
when isdate(substring(a.dt,7,4)+substring(a.dt,1,2)+substring(a.dt,4,2)+substring(a.dt,11,9)) <> 1
then 0
else 1
end
from
( -- Test Data
select dt = '08/31/2001 14:34:56' union all
select dt = '08/31/2001 34:34:56' union all
select dt = '08/31/2001 14:64:56' union all
select dt = '08/31/2001 14:34:66' union all
select dt = '08/31/2001 25:34:56' union all
select dt = '08/31/2001 14:34:56' union all
select dt = '13/01/2001 14:34:56' union all
select dt = '0a/31/2001 14:34:56' union all
select dt = '08/32/2001 14:34:56' union all
select dt = '21/31/2001 14:34:56'
) a

Results:
dt                  IsValidDateTime 
------------------- ---------------
08/31/2001 14:34:56 1
08/31/2001 34:34:56 0
08/31/2001 14:64:56 0
08/31/2001 14:34:66 0
08/31/2001 25:34:56 0
08/31/2001 14:34:56 1
13/01/2001 14:34:56 0
0a/31/2001 14:34:56 0
08/32/2001 14:34:56 0
21/31/2001 14:34:56 0

(10 row(s) affected)







That would allow 30th February as a valid date. . . In fact, it'd allow 39th February



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1183846
Posted Friday, September 30, 2011 12:24 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 3,081, Visits: 11,230
Cadavre (9/30/2011)
Michael Valentine Jones (9/30/2011)
Here is a fairly simple check that will work with any setting of DATEFIRST:
select
a.dt,
IsValidDateTime =
case
-- Check that date string is in correct format, mm/dd/yyyy hh:mm:ss
when dt not like '[0-1][0-9]/[0-3][0-9]/[1-9][0-9][0-9][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9]'
then 0
-- Reformat and test date in universal format of yyyymmdd hh:mm:ss
when isdate(substring(a.dt,7,4)+substring(a.dt,1,2)+substring(a.dt,4,2)+substring(a.dt,11,9)) <> 1
then 0
else 1
end
from
( -- Test Data
select dt = '08/31/2001 14:34:56' union all
select dt = '08/31/2001 34:34:56' union all
select dt = '08/31/2001 14:64:56' union all
select dt = '08/31/2001 14:34:66' union all
select dt = '08/31/2001 25:34:56' union all
select dt = '08/31/2001 14:34:56' union all
select dt = '13/01/2001 14:34:56' union all
select dt = '0a/31/2001 14:34:56' union all
select dt = '08/32/2001 14:34:56' union all
select dt = '21/31/2001 14:34:56'
) a

Results:
dt                  IsValidDateTime 
------------------- ---------------
08/31/2001 14:34:56 1
08/31/2001 34:34:56 0
08/31/2001 14:64:56 0
08/31/2001 14:34:66 0
08/31/2001 25:34:56 0
08/31/2001 14:34:56 1
13/01/2001 14:34:56 0
0a/31/2001 14:34:56 0
08/32/2001 14:34:56 0
21/31/2001 14:34:56 0

(10 row(s) affected)







That would allow 30th February as a valid date. . . In fact, it'd allow 39th February


No it wouldn't. You could have tested that before you posted.





Post #1183882
Posted Friday, September 30, 2011 1:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 2,404, Visits: 7,311
Michael Valentine Jones (9/30/2011)
No it wouldn't. You could have tested that before you posted.


Yep, or I should've just read the query properly! Didn't see the part of the case statement where you used the ISDATE function on the formatted date. Sorry



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1183903
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse