How to convert YYYY-MM-DD HH:MM:SS TO MM-DD-YYYY HH:MM:SS IN SQL Server?

Question

How to convert yyyy-mm-dd hh:mm:ss (in 24 hours format) to dd-mm-yyyy hh:mm:ss (in 24 hours format? I am using Sql Server 2008.

Given Date Format: 2017-12-18 18:16:49 - Its in DateTime format
Required Date Format: 18-12-2017 18:16:49

1. Tag the Sql Server version you are using. 2. Provide the data type of the column you are querying. — Igor, May 18 '18 at 11:25
Also, change your question from MM-DD-YYYY HH:MM:SS to DD-MM-YYYY HH:MM:SS. No way the required date format matches what you had asked. — Abhishek, May 18 '18 at 11:27
It can be done as follows: select CONVERT(VARCHAR(10), GETDATE(), 103) + ' ' + convert(VARCHAR(8), GETDATE(), 14) — Sandeep, May 18 '18 at 11:27
Where possible, I would format a datetime data type at the consumer / presentation end, not within the database query — Andrew, May 18 '18 at 11:31
@Sandeep.. I tried your approach, but the result is in this format - '18/12/2017 13:45:29'. I require '18-12-2017 13:45:29' — Md Kamran Azam, May 18 '18 at 11:31
Is your column an actual `datetime` or `datetime2` type or is it of type `varchar`? — Igor, May 18 '18 at 11:33
`SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103),'/','-') + ' ' + convert(VARCHAR(8), GETDATE(), 14)` — Abhishek, May 18 '18 at 11:34
@MdKamranAzam Update the query to: select CONVERT(VARCHAR(10), GETDATE(), 105) + ' ' + convert(VARCHAR(8), GETDATE(), 14) And it will replace the forward slash with the hyphen. — Sandeep, May 18 '18 at 11:35
Thanks to all. I got the solution from @Tanveer Singh Bhatia — Md Kamran Azam, May 18 '18 at 11:37

score 0 · Answer 1 · answered May 18 '18 at 11:25

0

Try this

SELECT FORMAT(CAST('2017-12-18 18:16:49' AS datetime) , 'dd-MM-yyyy HH:mm:ss')

answered May 18 '18 at 11:25

Sreenu131

2,476
1
7
18

Getting an error 'FORMAT' is not a recognized built-in function name. Severity 15 State 10 – Md Kamran Azam May 18 '18 at 11:27
@MdKamranAzam i think using lower version sql server 2012 – Sreenu131 May 18 '18 at 11:28
4

@MdKamranAzam, you should tag your question with the version of SQL Server you are using as the best solution will vary depending on available features. – Dan Guzman May 18 '18 at 11:29
@Sreenu131. I am using SQL server 2008. – Md Kamran Azam May 18 '18 at 11:32

score 0 · Answer 2 · edited May 18 '18 at 11:27

0

DECLARE @date DATETIME = '2017-12-18 18:16:49'

SELECT FORMAT(@date,'dd-MM-yyyy HH:mm:ss')

edited May 18 '18 at 11:27

Sreenu131

2,476
1
7
18

answered May 18 '18 at 11:25

Sameer

349
4
12

Tanveer Singh Bhatia · Accepted Answer · 2018-05-18T11:37:13.417

0

This Would work in Older SQL Server Versions Also, Converted to datetime first if it's VARCHAR otherwise you can skip that conversion.

SELECT convert(varchar,convert(datetime,'2017-12-18 18:16:49'),105) + ' ' + 
convert(varchar(8),convert(datetime,'2017-12-18 18:16:49'),14);

Use this Link as Reference for Date & Time conversion Formats

edited May 18 '18 at 11:37

answered May 18 '18 at 11:33

Tanveer Singh Bhatia

426
2
8

Thanks Tanveer. Exactly what I was finding. – Md Kamran Azam May 18 '18 at 11:36
You Are Welcome, Do use the Links provided for future reference – Tanveer Singh Bhatia May 18 '18 at 11:38

score 0 · Answer 4 · answered May 18 '18 at 12:04

I would advice not change directly in SQL formate, instead you could change your php code where query fetch the date data

for example you could assign your $yourDate object to new dateTime, and use formate function to define the date format:

<?php
$yourDate = new DateTime();
$timestring = $yourDate->format('m-d-Y h:i:s');
echo $timestring;

the output will be: 05-18-2018 05:00:34

You could take reference in this older discussion

This will prevent some bug error might be occurred if your data table has some other date format relative to, and also it is more useful once you need to have change the date format again

Hope this will be help

How to convert YYYY-MM-DD HH:MM:SS TO MM-DD-YYYY HH:MM:SS IN SQL Server?

4 Answers4