Post reply

String to Date without time-Sql server 2012

  • September 15, 2015 at 7:10 am

    #303086

    I have string as '1-12-2012', i wanted to convert it into date as format '2012-12-01'. I used the function as cast('1-12-2012' as Date) it's working but for a set of values where Null occurs it gives error as conversion failed when casting date and/or time from character string.

    How can i get rid of this situation.

  • September 15, 2015 at 7:18 am

    #1826724

    That code shouldn't fail with a NULL value, or are you saying you have the string value 'NULL' present in your data?

    DECLARE @DateAsString varchar(20) = NULL;

    SELECT CAST(@DateAsString AS DATE)

    Returns NULL, no error

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 15, 2015 at 7:27 am

    #1826732

    Use Try_Convert instead because it will not give you any error of type casting.

    Below will give you understanding

    Declare @T table ( stringDate varchar(20))

    insert into @T

    select '2-12-2012' union all

    select '11-12-2015' union all

    select '12-12-2012' union all

    select 'NULL' union all

    select '112-12-2012'

    select TRY_CONVERT(date, stringDate)

    from @T

    hope it helps

  • September 15, 2015 at 7:45 am

    #1826742

    Dates do not have formats. Strings that represent dates have formats, but dates themselves do not have formats.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • September 15, 2015 at 4:27 pm

    #1826915

    sqlinterset (9/15/2015)

    I used the function as cast('1-12-2012' as Date)

    That format is "ambiguous" and will depend on your Locale setting (which is both server-dependent and depends on the language of the currently connected users etc.

    Only unambiguous formats for DATE datatype is 'yyyymmdd' or 'yyyy-mm-dd' (please note that for DATETIME datatype only 'yyyymmdd' is unambiguous, so you might prefer to use that for DATE too to avoid the risk of accidental assignment to a DATETIME.

    You can force the parsing of a date using SET DATEFORMAT

    Here's an example:

    SET LANGUAGE 'British'

    GO

    select cast('2012-12-31' as Date) AS [GBUnambiguous]

    GO

    select cast('12-31-2012' as Date) AS [GBAmbiguous] -- Error!

    GO

    SET DATEFORMAT MDY

    select cast('12-31-2012' as Date) AS [GBAmbiguous]

    GO

    SET LANGUAGE 'English'

    GO

    select cast('2012-12-31' as Date) AS [USUnambiguous]

    GO

    select cast('12-31-2012' as Date) AS [USAmbiguous]

    GO

  • September 15, 2015 at 4:42 pm

    #1826921

    sqlinterset (9/15/2015)

    I have string as '1-12-2012', i wanted to convert it into date as format '2012-12-01'. I used the function as cast('1-12-2012' as Date) it's working but for a set of values where Null occurs it gives error as conversion failed when casting date and/or time from character string.

    How can i get rid of this situation.

    What format are the incoming dates in? mm-dd-yyyy or dd-mm-yyyy? I ask because casting a real NULL to DATE does NOT cause an error. If you're getting errors, something else is wrong.

    --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

Viewing 6 posts - 1 through 5 (of 5 total)

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