h1

SQL SERVER – Get Time in AM/PM Format

12/22/2008

Today i come with one situation where I need to get Time in AM/PM format.

I tried with different way and come up with the simple solution, that i am going to share with all of you.


SELECT
GETDATE() AS CurrentDate,
RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentTime,
CONVERT(VARCHAR(10), GETDATE(), 101) + + RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentDateTime

SQL DateTime

Syntax to Get SQL DateTime With Am/Pm Format

SQL Date time in AM/PM Format

Get SQL Date time in AM/PM Format

I have read very interesting article on SQL Authority.Com . You can find the many ways to get date in different formats.

About these ads

11 comments

  1. Note that if you format dates using sql, all your DATEs become VARCHARs which can’t be used to make use of date related functions until re-converted back to DATETIME

    If you use front end application, you should do formation there

    Madhivanan


  2. That worked fine. Thanks!


  3. GUD


  4. Really its very useful thank you so much


  5. Its Good.


  6. Hey Thanks….

    Worked well for me….Got me what i needed …

    God Bless


  7. hi,
    when i inserted a new rows, i cant get the date time format like 08/08/201212:49PM.
    if i wrote this query in sql (SELECT CONVERT(VARCHAR(10), GETDATE(), 101) + RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) got output like 08/08/201212:49PM..
    but i inserted means
    (insert into j(createtime)values(CONVERT(VARCHAR(10), GETDATE(), 101) + RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) )
    ) the output is Conversion failed when converting date and/or time from character string.

    Please anyone can help me


  8. Hi Meena,

    I have found that, it is working with SQL 2005 and SQL 2008. Please find my scripts:

    DECLARE @test TABLE(T DATETIME)

    INSERT INTO @test(t)
    VALUES (CONVERT(VARCHAR(10), GETDATE(), 101) + RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7))

    SELECT * FROM @test

    Can you please check what is the datatype of column “CreateTime” on your table “j”?

    Thanks,
    Tejas
    SQLYoga.com


  9. It worked .. Thank you..


  10. Thanks, just what I needed.


  11. Thank you… in fact, your code was added to my time manipulation sql template… job well done. Sharing….

    DECLARE @dt DATETIME
    SET @dt = GETDATE()

    /*************************************************************************************
    http://tejasnshah.wordpress.com/2008/12/22/sql-server-get-time-in-ampm-format/
    *************************************************************************************/
    –SHOWS A PERFECT AM/PM
    SELECT @dt [dt]
    ,RIGHT(CONVERT(VARCHAR, @dt, 100),7) AS AMPM

    /*************************************************************************************
    http://gallery.technet.microsoft.com/scriptcenter/Time-Calculations-Rounding-59f9312a
    *************************************************************************************/

    –Removing the time portion / zero the time
    SELECT @dt [dt]
    ,DATEADD(DAY,-1,DATEDIFF(DAY,0,@dt)) [YesterdayZeroHour]
    ,DATEADD(DAY,0,DATEDIFF(DAY,0,@dt)) [TodayZeroHour]
    ,DATEADD(DAY,1,DATEDIFF(DAY,0,@dt)) [TomorrowZeroHour]
    ,CAST(DATEADD(DAY,-1,CAST(@dt AS DATE)) AS DATETIME) [YesterdayZeroHour] –USING DATE TYPE 2008+
    ,CAST(CAST(@dt AS DATE) AS DATETIME) [TodayZeroHour] –USING DATE TYPE 2008+
    ,CAST(DATEADD(DAY,1,CAST(@dt AS DATE)) AS DATETIME) [TomorrowZeroHour] –USING DATE TYPE 2008+

    –Round time down to different boundaries
    SELECT @dt [dt]
    ,DATEADD(n,(DATEDIFF(n,0,@dt)/5*5),0) [Rounded Down 05 Min]
    ,DATEADD(n,(DATEDIFF(n,0,@dt)/6*6),0) [Rounded Down 06 Min]
    ,DATEADD(n,(DATEDIFF(n,0,@dt)/10*10),0) [Rounded Down 10 Min]
    ,DATEADD(n,(DATEDIFF(n,0,@dt)/15*15),0) [Rounded Down 15 Min]
    ,DATEADD(n,(DATEDIFF(n,0,@dt)/20*20),0) [Rounded Down 20 Min]
    ,DATEADD(n,(DATEDIFF(n,0,@dt)/30*30),0) [Rounded Down 30 Min]

    –Round time up to different boundaries
    SELECT @dt [dt]
    ,DATEADD(n,DATEDIFF(n,0,@dt)+(5-(DATEDIFF(n,0,@dt)%5)),0) [Rounded Up 05 Min]
    ,DATEADD(n,DATEDIFF(n,0,@dt)+(6-(DATEDIFF(n,0,@dt)%6)),0) [Rounded Up 06 Min]
    ,DATEADD(n,DATEDIFF(n,0,@dt)+(10-(DATEDIFF(n,0,@dt)%10)),0) [Rounded Up 10 Min]
    ,DATEADD(n,DATEDIFF(n,0,@dt)+(15-(DATEDIFF(n,0,@dt)%15)),0) [Rounded Up 15 Min]
    ,DATEADD(n,DATEDIFF(n,0,@dt)+(20-(DATEDIFF(n,0,@dt)%20)),0) [Rounded Up 20 Min]
    ,DATEADD(n,DATEDIFF(n,0,@dt)+(30-(DATEDIFF(n,0,@dt)%30)),0) [Rounded Up 30 Min]



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: