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

Solution 1:

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"=CASEWHEN patindex('%AM', CONVERT(VARCHAR(20), GETDATE(), 100) ) =0THENCONVERT(VARCHAR(20),getdate(),107) +'  '+SUBSTRING(CONVERT(VARCHAR(10),getdate(),108),2,0)+
       REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(25),getdate(),100),7)),'PM','AM')

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

  END

Solution 2:

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()))