convert datetime to varchar(50)

Question

Need help in converting datetime to varchar as in given format

2015-01-04 16:07:37.000"

to

01/04/2015 16:07PM

Here is what tried:

convert(varchar(20),datetime,103)+ ' '+convert(varchar(20),datetime,108)+ ' ' +right(convert(varchar(30),datetime,109),2)

I had tried "convert(varchar(20),datetime,103)+ ' '+convert(varchar(20),datetime,108)+ ' ' +right(convert(varchar(30),datetime,109),2)" — user3244104, Jun 22 '15 at 12:35
You may use this for date time conversion : http://www.sqlusa.com/bestpractices/datetimeconversion/ AND http://stackoverflow.com/a/19537658/4609016 — User Learning, Jun 22 '15 at 12:40

Ameya Deshpande · Accepted Answer · 2015-06-22T12:42:29.700

1

this will work in sqlserver SQLFiddle regarding this Demo

SELECT convert(varchar, getdate(), 103) 
+' '+ CONVERT(varchar(15),CAST(getdate() AS TIME),100)

edited Jun 22 '15 at 12:42

answered Jun 22 '15 at 12:38

Ameya Deshpande

3,580
4
30
46

1

@user3244104 it ll show exact output , u need to change according to ur variable from getdate() to urs datetime ... – User Learning Jun 22 '15 at 12:45
Thanks a lot for your help – user3244104 Jun 22 '15 at 12:52
@user3244104 glad it helps you can mark it as answer :) – Ameya Deshpande Jun 22 '15 at 12:53

Peter Campbell · Answer 2 · 2015-06-22T13:32:26.133

1

This will get your string from current datetime in the format 06/22/15 1:46:07 PM.

 SELECT CONVERT(VARCHAR(50), GETDATE(), 22)

Try using Format

SELECT FORMAT(GETDATE(), 'g')

which will get 6/22/2015 1:57 PM.

edited Jun 22 '15 at 13:32

answered Jun 22 '15 at 12:44

Peter Campbell

661
1
7
35

score 0 · Answer 3 · answered Jun 22 '15 at 13:13

You can always build it yourself using DATEPART...

SELECT
    RIGHT('0' + CONVERT(nvarchar(max), DATEPART(m, Date_Field)), 2) + '/' + 
    RIGHT('0' + CONVERT(nvarchar(max), DATEPART(d, Date_Field)), 2) + '/' + 
    CONVERT(nvarchar(max), DATEPART(yyyy, Date_Field))  + ' ' + 
    RIGHT('0' + CONVERT(nvarchar(max), DATEPART(hour, Date_Field)), 2) + ':' + 
    RIGHT('0' + CONVERT(nvarchar(max), DATEPART(minute, Date_Field)), 2) + 
    RIGHT(CONVERT(VARCHAR(30), Date_Field, 9), 2)

Then you can use custom delimiters between the date and times, add seconds (or milliseconds, etc.) as you need. Wrap it in a UDF if you find the need to use it a lot.

convert datetime to varchar(50)

3 Answers3