Post reply

Convert

  • March 27, 2014 at 10:05 am

    #291547

    I am using DATEADD(Hour,-5,Time1)CSTTime to do the calculations and show the date in CST Format.

    I need to display the time as 2:00 PM Instead of 14:01:00.0000000.

    Pls advise.

  • March 27, 2014 at 10:06 am

    #1700769

    It should be 2:01

  • March 27, 2014 at 10:14 am

    #1700773

    Have you read the page on CAST and CONVERT in Books Online?

    John

  • March 27, 2014 at 11:08 am

    #1700808

    Yes, Infact using this: CONVERT(Varchar,(DATEADD(Hour,-5,Time1)),100)NewCSTTime resolves but then when I implement this same with 5.30 it rounds to 6 hours.

    Tried DAteadd with Minute , 30 and it works fine by itself...

    DOn't know how should I merge with Hour

  • March 27, 2014 at 11:10 am

    #1700812

    See if this will work for you

    DECLARE @DateC DATETIME = '2014-03-27 3:51:08.997PM'

    ,@TimePart VARCHAR(20)

    SET @TimePart = CONVERT(VARCHAR(20),CAST(DATEADD(HOUR,5,@DateC) AS TIME),100)

    /* AMPM format */

    SELECT CONVERT(VARCHAR,CONVERT(DATE, @DateC)) + ' ' + CONVERT(VARCHAR,@TimePart)

    /* 24hr format */

    SELECT CONVERT(DATETIME,DATEADD(Hour,5,@DateC),121)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 27, 2014 at 11:11 am

    #1700815

    This worked!

    Convert(Varchar,(DateAdd(HH,05,DateAdd(MINUTE ,30,Time1))),100)NewTime

  • March 27, 2014 at 11:12 am

    #1700816

    Thank you.

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

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