how to convert '2012-02-15 14:26:58.520' into '2/15/2012 2:00:00 PM'

  • how to convert 2012-02-15 14:26:58.520 into 2/15/2012 2:00:00 PM? Please help me with the exact syntax for this. Just highlighting one thing, for any time instance, where the hour is 14 hours and not 15, it has to convert in 2:00:00 PM only. I dont want the minutes.

    Regards,

    Ganesh.

  • CONVERT(datetime, '2006-04-25T15:50:59.997', 131)

    SELECT convert(varchar, getdate(), 111

    )

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

    any queries please post !

  • Sorry Kshitiz, no luck yet.

  • I like this one:

    SELECT DATEADD(hour, DATEDIFF(hour, 0, '2012-02-15 14:26:58.520'), 0)

    Of course, if you want the PM part, you still need to convert.


    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

  • SELECT CONVERT(nvarchar(MAX), GETDATE(), 101) + ' ' + Stuff(Right(convert(nvarchar(max),DATEADD(hour, DATEDIFF(hour, 0, GETDATE()), 0),109),13),6,4,'')

    Regards

    Guru

  • Dwain's method is more efficient, since it doesn't involve string conversions. Granted, it doesn't give you the AM/PM, but do you really need that?

    If you want proof, run this in a database that has lots of objects in it:

    SET STATISTICS TIME ON

    SELECT CONVERT(nvarchar(MAX),create_date,101) + ' ' + Stuff(Right(convert(nvarchar(max),DATEADD(hour,DATEDIFF(hour, 0, create_date), 0),109),13),6,4,'')

    FROM sys.objects

    ... and then this:

    SET STATISTICS TIME ON

    SELECT DATEADD(hour, DATEDIFF(hour, 0, create_date), 0)

    FROM sys.objects

    John

  • Why in SQL? It's not a formatting tool!

    You should do it in the UI or report.

    If you insist on exact format requested (eg. no leading 0 for month part) your only way is to build the whole string yourself:

    declare @dt datetime = '2012-02-15 14:06:08.520'

    select cast(month(@dt) as varchar) + '/' +

    cast(day(@dt) as varchar) + '/' +

    cast(year(@dt) as varchar) + ' ' +

    ltrim(reverse(substring(reverse(convert(varchar, @dt, 100)),6,2))) + ':00:00 ' +

    right(convert(varchar, @dt, 100),2)

    But again, you should do it in UI!!!

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Dwain's reply is very close to requirement, without AM\PM, but I think this is fine for the moment.

    Heartly thanks to everyone for your sincere involvement.

  • Messy but works

    DECLARE @Date DATETIME

    DECLARE @tab TABLE (Y VARCHAR(4), M VARCHAR(2), D VARCHAR(2), T VARCHAR(11))

    SELECT @Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE() ), 0)

    INSERT INTO

    @tab

    SELECT

    DATEPART(YEAR,@DATE),

    DATEPART(MONTH,@DATE),

    DATEPART(DAY,@DATE),

    CASE DATEPART(HOUR,@DATE)

    WHEN 00 THEN '00:00:00 AM'

    WHEN 01 THEN '01:00:00 AM'

    WHEN 02 THEN '02:00:00 AM'

    WHEN 03 THEN '03:00:00 AM'

    WHEN 04 THEN '04:00:00 AM'

    WHEN 05 THEN '05:00:00 AM'

    WHEN 06 THEN '06:00:00 AM'

    WHEN 07 THEN '07:00:00 AM'

    WHEN 08 THEN '08:00:00 AM'

    WHEN 09 THEN '09:00:00 AM'

    WHEN 10 THEN '10:00:00 AM'

    WHEN 11 THEN '11:00:00 AM'

    WHEN 12 THEN '12:00:00 PM'

    WHEN 13 THEN '01:00:00 PM'

    WHEN 14 THEN '02:00:00 PM'

    WHEN 15 THEN '03:00:00 PM'

    WHEN 16 THEN '04:00:00 PM'

    WHEN 17 THEN '05:00:00 PM'

    WHEN 18 THEN '06:00:00 PM'

    WHEN 19 THEN '07:00:00 PM'

    WHEN 20 THEN '08:00:00 PM'

    WHEN 21 THEN '09:00:00 PM'

    WHEN 22 THEN '10:00:00 PM'

    WHEN 23 THEN '11:00:00 PM'

    END

    SELECT

    M+'/'+D+'/'+Y+' '+T AS DT

    FROM

    @tab

Viewing 9 posts - 1 through 8 (of 8 total)

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