Covert a string to simple sql datetime format

  • Jeff Sims-413169

    SSChasing Mays

    Points: 601

    Can anyone help me out here.  I need to convert strings in a file I received to datetime.  An example format is: Wed Jun 29 15:57:45 2019

  • frederico_fonseca

    SSChampion

    Points: 14686

    if format is always the same you will need to use a bit of substrings and concatenation

    extract following blocks

    • year
    • month
    • day
    • time

    then concatenate year-month-day  time with between day and time  (and "-" on the others)

    then use convert to datetime with format 120 (https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#date-and-time-styles)

     

  • Jonathan AC Roberts

    SSCoach

    Points: 17296

    DECLARE @DateString nvarchar(100)='Wed Jun 29 15:57:45 2019'
    SELECT CONVERT(datetime,SUBSTRING(@DateString,5,100))
  • Jeff Moden

    SSC Guru

    Points: 996645

    Jonathan AC Roberts wrote:

    DECLARE @DateString nvarchar(100)='Wed Jun 29 15:57:45 2019'
    SELECT CONVERT(datetime,SUBSTRING(@DateString,5,100))

    I've always been astounded by the implicit conversions of strings to DateTime that SQL Server handles correctly.  It seriously outstrips the formatting available through CONVERT and people seem to thing that's all it can handle for string conversions.

    I have to admit that I've not seen this format before but, like you, I would have tried it anyway and would have, once again, been astounded by its implicit conversion capabilities.

    Nicely done, Jonathan.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This was removed by the editor as SPAM

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

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