SQL SERVER – Get Time in AM/PM Format
12/22/2008Today 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
Syntax to Get SQL DateTime With 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.
Tejas Shah
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
That worked fine. Thanks!
GUD
Really its very useful thank you so much
Its Good.
Hey Thanks….
Worked well for me….Got me what i needed …
God Bless
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
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
It worked .. Thank you..
Thanks, just what I needed.