Post reply

Casting Time String

  • May 3, 2004 at 12:44 pm

    #179630

    The sp_help_job procedure returns the last_run_date and last_run_time in the following format:

    20040503 83811

    I would need this in this format:

    2004-05-03 08:38:11.000

    Here is what I came up with

    SELECT CAST ( '20040503 ' + STUFF ( STUFF (RIGHT ( '000000' + '83811', 6), 3, 0, ':'), 6,0,':') AS DateTime)

    Is there any way I can cast the time string without embedding with ':'.

  • May 3, 2004 at 4:47 pm

    #505053

    Try using CONVERT instead of CAST.  Like SELECT CONVERT ( datetime, '20040503',101)

    101 is the format code for the date format you are searching for. 

  • May 4, 2004 at 8:35 am

    #505145

    My question was regarding casting / converting

    20040503 83811 into the format of

    2004-05-03 08:38:11.000

    The solution in my mail with CAST works. I am looking for a better way !

  • May 4, 2004 at 10:56 am

    #505166

    Well, here's another way, but it's perhaps more convoluted than yours.  I think you're stuck with one of these or some other variant that breaks up that '83811'.  I don't think there's a way to let SQL Server know that some of those digits are hours, some are minutes, and some are seconds without some sort of parsing.

        DECLARE @v Varchar(6)
    , @t DATETIME
        SET @v = '83811'
        IF LEN(@v) = 5 
        SET @v = '0' + @v
        SET @t = CAST(LEFT(@v,2) AS FLOAT) /24 + CAST(SUBSTRING(@v,3,2) AS FLOAT)/(24*60) + CAST (RIGHT(@v,2) AS FLOAT)/(24*60*60)
    SELECT @t, CONVERT(Varchar(20), @t, 108)

    Ugly, huh?

  • May 6, 2004 at 7:32 am

    #505475

    Don't know if the following will help.  I got it from Microsoft's web site.

    convert(datetime,term_date+" "+

              substring(term_time,1,2)+":"+

              substring(term_time,3,2)+":"+

              substring(term_time,5,2)   )

     

    given that term_time has 6 characters (I was thinking about something like 00:00:23 which will only show as 23, which will require some left padding).

    Anyway, the URL is http://support.microsoft.com/default.aspx?scid=kb;en-us;69133&Product=sql2k

  • May 6, 2004 at 9:24 am

    #505501

    if you can change the format of the input date this will work:

    select convert(datetime, '20040503 08:38:15.333', 13)

    13 is the correct codepage not 101 (mm/dd/yyyy)

    cheers

    dbgeezer

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

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