1

I am working on a query in SQL server 2012 where I need to get datetime format as 01/03/2017 16:06:21 AM.

How do I do that?

Matt
  • 14,906
  • 27
  • 99
  • 149
toby
  • 59
  • 4

3 Answers3

2

If you really do want 24 hour format with AM/PM and you want to do this only using Convert, try:

for mm/dd/yyyy HH:mm:ss AM

select convert(varchar(10),getdate(),101) + ' ' 
+ convert(varchar(8),getdate(),114) + ' ' 
+ RIGHT( convert(varchar,getdate(),9),2)

for dd/mm/yyyy HH:mm:ss AM

select convert(varchar(10),getdate(),103) + ' ' 
+ convert(varchar(8),getdate(),114) + ' ' 
+ RIGHT( convert(varchar,getdate(),9),2)
chrisuae
  • 1,092
  • 7
  • 8
0

In sql server 2012+ you can use format()

for dd/MM:

select format(getdate(), 'dd/MM/yyyy hh:mm:ss tt')

for MM/dd:

select format(getdate(), 'MM/dd/yyyy hh:mm:ss tt')

For 24 hour format, switch to HH instead of hh, but then why would you need AM/PM?

for dd/MM:

select format(getdate(), 'dd/MM/yyyy HH:mm:ss tt')

for MM/dd:

select format(getdate(), 'MM/dd/yyyy HH:mm:ss tt')
SqlZim
  • 37,248
  • 6
  • 41
  • 59
0

Try this code and change the numbers to Format

Select  CONVERT(VARCHAR,GETDATE(),21)
//OutPut---------------
21=2017-03-14 19:17:28
//-----------------------------
  0=Mar 14 2017  7:17PM
    1=03/14/17
    2=17.03.14
    3=14/03/17
    4=14.03.17
    5=14-03-17
    6=14 Mar 17
    7=Mar 14, 17
    8=19:17:28
    9=Mar 14 2017  7:17:28:467PM
    10=03-14-17
    11=17/03/14
    12=170314
    13=14 Mar 2017 19:17:28:470
    14=19:17:28:473
    20=2017-03-14 19:17:28
    21=2017-03-14 19:17:28.477
    22=03/14/17  7:17:28 PM
    23=2017-03-14
    24=19:17:28
    25=2017-03-14 19:17:28.480
    100=Mar 14 2017  7:17PM
    101=03/14/2017
    102=2017.03.14
    103=14/03/2017
    104=14.03.2017
    105=14-03-2017
    106=14 Mar 2017
    107=Mar 14, 2017
    108=19:17:28
    109=Mar 14 2017  7:17:28:497PM
    110=03-14-2017
    111=2017/03/14
    112=20170314
    113=14 Mar 2017 19:17:28:503
    114=19:17:28:503
Mohamed Ageeb
  • 21
  • 6