CODEWITHSUNDEEP
sql-serverdatetime
r.paul Ǿ
r.paul Ǿ

Reputation: 111

SQL DateTime Format Convert

i want to change my datetime format on my MS SQL from the default format of 12-12-2000 13:01:01:0111 to December 12, 2000 1:01AM

this is my codes->

date_issued = CONVERT(VARCHAR(20),date_issued,107) +' '+
              SUBSTRING(CONVERT(VARCHAR(10),date_issued,108),2,0)+
              LTRIM(RIGHT(CONVERT(VARCHAR(25),date_issued,100),7)),

How can I convert it to December 12, 2000 1:01AM???

Upvotes: 1

Views: 1116

Answers (2)

aads
aads

Reputation: 2495

If the issue is to convert 'PM' text to 'AM', then simply use 'REPLACE', note that i used 'GETDATE()' in below examples

  CONVERT(VARCHAR(20),getdate(),107) +'  '+
  SUBSTRING(CONVERT(VARCHAR(10),getdate(),108),2,0)+
  REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(25),getdate(),100),7)),'PM','AM')

If its PM->AM and AM->PM then try below

SELECT  "DateTime"=  
  CASE 
     WHEN patindex('%AM', CONVERT(VARCHAR(20), GETDATE(), 100) ) =  0 THEN

       CONVERT(VARCHAR(20),getdate(),107) +'  '+
       SUBSTRING(CONVERT(VARCHAR(10),getdate(),108),2,0)+
       REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(25),getdate(),100),7)),'PM','AM')

     ELSE 

       CONVERT(VARCHAR(20),getdate(),107) +'  '+
       SUBSTRING(CONVERT(VARCHAR(10),getdate(),108),2,0)+
       REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(25),getdate(),100),7)),'AM','PM')

  END

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44336

Assuming you are using a datetime field, you are not going to change the format of the date being stored that way.

You can achieve your output format this way, I used getdate because i am not sure what you are trying to do:

SELECT stuff(convert(varchar(25), getdate(), 100), 
       1, 3, datename(month, getdate()))

Upvotes: 1

Related Questions