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

Need to find out whether date is of which data type Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 12:58 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
AS you've found out, the use of ISDATE is unreliable when doing implicit conversions.

Try these:

SELECT ISDATE('2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT ISDATE('2013-01-08 15:44:12.2081606') AS test
SELECT ISDATE('2013-01-08 15:44:12.208') AS test

The different results you get when trying to test a date's validity depends on the datatype.

Try these and see what happens:

SELECT CONVERT(SMALLDATETIME,'2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT CONVERT(DATETIME,'2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT CONVERT(DATETIME2,'2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT CONVERT(DATETIMEOFFSET,'2013-01-08 15:44:12.2081606 +05:30') AS test

Here are two procedures that will test for valid dates. You can modify these to test for various datatypes as you are trying to do. These procedures avoid using the ISDATE function and simply attempt to convert what may or may not be a valid date into a valid datetime datatype. The first procedure tests based on the DATETIMEOFFSET datatype which is very forgiving and will return as a valid date something like '1234' as '1234-01-01 00:00:00.0000000 +00:00' which is in fact a valid date.

The second procedure tests based on the less forgiving SMALLDATETIME datatype, but also calls the first procedure if you want to check a date both ways. Of course, this second procedure can be modified to skip the call to the external procedure and the datatypes can be changed as well. But doing so may give different results!

I've also included a parameter in the second procedure so that the DATEFORMAT can be specified with a default of 'DMY'.


CREATE PROCEDURE [dbo].[IsValidDate]

@sDate VARCHAR(50)
,@sDateFormat CHAR(3) = 'DMY' -- MDY, DMY, YMD, YDM, MYD, DYM

AS
BEGIN

SET NOCOUNT ON
SET DATEFORMAT @sDateFormat

DECLARE
@dStdDate SMALLDATETIME
,@dUMCDate DATETIMEOFFSET
,@bIsValidUMCDate BIT
,@bIsValidDate BIT

SET @dStdDate = NULL
SET @dUMCDate = NULL


/* This section is optional and could be left out */
/* Check to see if this is a valid UMC date */

IF OBJECT_ID('tempdb..#CheckUMCDate') IS NOT NULL
DROP TABLE #CheckUMCDate

CREATE TABLE #CheckUMCDate (
[ID] INT IDENTITY(1,1) NOT NULL,
[InputDate] VARCHAR(50) NULL,
[ConvertedDate] VARCHAR(50) NULL,
[IsValidUMCDate] BIT NULL
PRIMARY KEY (ID))


BEGIN TRY
INSERT INTO #CheckUMCDate
EXEC dbo.IsValidUMCDate @sDate
END TRY
BEGIN CATCH
INSERT INTO #CheckUMCDate
SELECT
@sDate AS InputDate
,NULL ConvertedDate
,0 AS IsValidUMCDate
END CATCH

SELECT
@dUMCDate = ConvertedDate
,@bIsValidUMCDate = IsValidUMCDate
FROM
#CheckUMCDate

/* End of optional section */


/* Check using regular SMALLDATETIME datatype */

BEGIN TRY
SET @dStdDate = CONVERT(SMALLDATETIME,@sDate)
SET @bIsValidDate = 1
END TRY
BEGIN CATCH
SET @bIsValidDate = 0
SET @dStdDate = NULL
END CATCH

SELECT
@sDate AS InputDate
,@dStdDate AS StdDate
,@bIsValidDate AS IsValidDate
,@dUMCDate AS UMCDate
,@bIsValidUMCDate AS IsValidUMCDate


/*
EXAMPLES:

EXEC dbo.IsValidDate '01-07-2001' -- Valid date
EXEC dbo.IsValidDate '1/7/2001' -- Valid date
EXEC dbo.IsValidDate '07-01-2001' -- Valid date
EXEC dbo.IsValidDate '7/1/2001' -- Valid date
EXEC dbo.IsValidDate '29-12-2013','DMY' -- Valid date in DMY format
EXEC dbo.IsValidDate '29-12-2013','MDY' -- Invalid date in MDY format
EXEC dbo.IsValidDate '32-12-2013' -- ERROR: Date out of range
EXEC dbo.IsValidDate '29-02-2013' -- ERROR: Not a leap year
EXEC dbo.IsValidDate 'xyz' -- ERROR: Invalid date
EXEC dbo.IsValidDate '1234' -- Invalid std date/valid umc date
EXEC dbo.IsValidDate '2013-01-08 15:44:12' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.000' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.208' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606' -- Invalid std date/valid umc date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30' -- Invalid std date/valid umc date

*/

END
GO

CREATE PROCEDURE [dbo].[IsValidUMCDate]

@sDate VARCHAR(50)

AS
BEGIN

SET NOCOUNT ON
SET DATEFORMAT DMY

DECLARE
@dUMCDate DATETIMEOFFSET
,@bIsValidUMCDate BIT
,@ERROR BIT

SET @dUMCDate = NULL

BEGIN TRY
SET @dUMCDate = CONVERT(DATETIMEOFFSET,@sDate)
SET @bIsValidUMCDate = 1
END TRY
BEGIN CATCH
SET @bIsValidUMCDate = 0
END CATCH

SET @ERROR = @bIsValidUMCDate

SELECT
@sDate AS InputDate
,@dUMCDate AS ConvertedDate
,@ERROR AS IsValidUMCDate



/*
EXAMPLES:

EXEC dbo.IsValidUMCDate '01-07-2001' -- Valid date
EXEC dbo.IsValidUMCDate '1/7/2001' -- Valid date
EXEC dbo.IsValidUMCDate '07-01-2001' -- Valid date
EXEC dbo.IsValidUMCDate '7/1/2001' -- Valid date
EXEC dbo.IsValidUMCDate '29-12-2013' -- Valid date
EXEC dbo.IsValidUMCDate '32-12-2013' -- ERROR: Date out of range
EXEC dbo.IsValidUMCDate '29-02-2013' -- ERROR: Not a leap year
EXEC dbo.IsValidUMCDate 'xyz' -- ERROR: Invalid date
EXEC dbo.IsValidUMCDate '1234' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.000' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.208' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.2081606' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.2081606 +05:30' -- Valid date


*/

END
GO


Post #1404962
Posted Wednesday, January 9, 2013 5:54 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: Today @ 3:30 AM
Points: 3,420, Visits: 5,348
Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1405092
Posted Thursday, January 10, 2013 1:51 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 2,127, Visits: 3,215
dwain.c (1/9/2013)
Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis.



You might want to review the new functions in SQL 2012 dealing with conversion issues first. It might save you unneeded effort .


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1405637
Posted Thursday, January 10, 2013 2:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
ScottPletcher (1/10/2013)
dwain.c (1/9/2013)
Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis.

You might want to review the new functions in SQL 2012 dealing with conversion issues first. It might save you unneeded effort .

Thanks Scott...I've just recently gotten my hands on a SQL2012 instance. I deal with over 100 different database servers and I just got my first client using SQL2012. It will be awhile before I can jump too far ahead. I'm still unable to use MERGE and a number of other 2008 additions for some of my clients because they are still on SQL2005.

Fortunately, I don't have to deal with any backward compatibility issues prior to that! Perhaps you could write an article on the new SQL2012 date functions? A quick search only showed one SQL Spackle Article on performance of 2012 date functions.

 
Post #1405653
Posted Thursday, January 10, 2013 2:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 2,127, Visits: 3,215
Steven Willis (1/10/2013)
ScottPletcher (1/10/2013)
dwain.c (1/9/2013)
Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis.

You might want to review the new functions in SQL 2012 dealing with conversion issues first. It might save you unneeded effort .

Thanks Scott...I've just recently gotten my hands on a SQL2012 instance. I deal with over 100 different database servers and I just got my first client using SQL2012. It will be awhile before I can jump too far ahead. I'm still unable to use MERGE and a number of other 2008 additions for some of my clients because they are still on SQL2005.

Fortunately, I don't have to deal with any backward compatibility issues prior to that! Perhaps you could write an article on the new SQL2012 date functions? A quick search only showed one SQL Spackle Article on performance of 2012 date functions.

 



In this case, I was thinking of the PARSE, TRY_PARSE and TRY_CONVERT functions. They can do the data type checking for you in SQL 2012.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1405668
Posted Thursday, January 10, 2013 5:32 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: Today @ 3:30 AM
Points: 3,420, Visits: 5,348
ScottPletcher (1/10/2013)
dwain.c (1/9/2013)
Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis.



You might want to review the new functions in SQL 2012 dealing with conversion issues first. It might save you unneeded effort .


Noted for future reference because, alas, at this time I must work only with the stone knives and bear skins available in SQL 2008.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1405709
Posted Friday, January 11, 2013 3:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 1:47 AM
Points: 196, Visits: 87
declare
@datetime datetimeoffset = '2012-10-25 00:00:00.000 +04:30'

select
case
when (DATEPART(TZ, @datetime) <> 0 and DATEPART(TZ, @datetime) is not null OR CONVERT(TIME(0),@datetime) <> '00:00:00')
then 0
when (DATEPART(TZ, @datetime) = 0 and @datetime <> '')
then 1
when (DATEPART(TZ, @datetime) is null)
then NULL
end as Precision
Post #1405859
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse