Need to find out whether date is of which data type

  • Suppose I have a variable which contains date value in a string format.

    eg: declare @value1 varchar(50) = '2013-01-08 15:44:12.2081606 +05:30'

    Now my question is, how will i come to know whether the @value1 containing the date value as shown above is of which type? and based on the type it should return null, 1 or 0.

    eg:

    for datetime and datetimeoffset it should return 1,

    for date type it should return 0,

    and if @value1 doesn't contain any value then it should return NULL.

  • try using isDate() function.

    or

    try to convert the string using Convert function then check with isdate function.

  • If i use isdate() function also will not solve my issue.

    my issue is to find out what is the datatype?

  • With difficulty

    You could do a check in the string for the + or - symbol to indicate Offset

    You could then do a check for a : to detail if a time has been put in the string for datetime

    You could check the length of the variable is 10, if the date has been provided only

  • anthony.green (1/8/2013)


    With difficulty

    You could do a check in the string for the + or - symbol to indicate Offset

    You could then do a check for a : to detail if a time has been put in the string for datetime

    You could check the length of the variable is 10, if the date has been provided only

    If user passes a string value with all these special characters will lead to wrong check.

    eg: 'sdfsdfsfs:sdfsf', '2323232:2323:23:23 +2323'

  • You will need ISDATE(), LEN, CHARINDEX as a minumum to get this working.

    But you should be doing your data validation in the front end, not within SQL.

  • I agree with Anthony that this should be done in the front end. But you may want to examine the following results and see if this gives you some ideas.

    WITH PotentialDates (d) AS (

    SELECT '2013-01-08 15:44:12.2081606 +05:30'

    UNION ALL SELECT '2013-01-08 15:44:12.2081606'

    UNION ALL SELECT '2013-01-08 15:44:12.208'

    UNION ALL SELECT '2013-01-08'

    UNION ALL SELECT 'YYYY-DD-MM HH:MM:SS.mmmmmm'

    )

    SELECT IsDate=ISDATE(d)

    ,PATINDEX('[0-9][0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9]', LEFT(d, 10))

    ,ISDATE(LEFT(d, 23))

    FROM PotentialDates

    I found it interesting and somewhat unexpected that ISDATE() didn't work on the first two.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • ISDATE

    Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

    ISDATE returns 0 if the expression is a datetime2 value.

    So as the first is a DateTimeOffset and the second is DateTime2, you would expect them to return 0.

    DECLARE @String VARCHAR(50) = '2013-01-08 15:44:12.20848489 -05:30'

    IF

    PATINDEX('%[A-Z]%',@String) = 0

    BEGIN

    SELECT

    CASE WHEN LEN(@String) = 10 THEN 'Date'

    WHEN LEN(@String) = 23 THEN 'DateTime'

    WHEN CHARINDEX('+',@String) > 0 THEN '+ Offset'

    WHEN CHARINDEX('-',REVERSE(@String)) <=6 THEN '- Offset'

    END AS WhatIsTheDataType

    END

    ELSE

    SELECT 'You have not supplied a valid date'

  • anthony.green (1/9/2013)


    ISDATE

    Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

    ISDATE returns 0 if the expression is a datetime2 value.

    You are assuming sir that I read BOL. 😛 It is much more fun to experiment. 😀


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (1/9/2013)


    anthony.green (1/9/2013)


    ISDATE

    Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

    ISDATE returns 0 if the expression is a datetime2 value.

    You are assuming sir that I read BOL. 😛 It is much more fun to experiment. 😀

    Yep you are assuming that I read BOL too, I also fell into this trap when first looking at this yesterday.

  • 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

  • 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![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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. :angry:

    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.

     

  • 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. :angry:

    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) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 15 posts - 1 through 15 (of 16 total)

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