CODEWITHSUNDEEP
sqlsql-servert-sqldateformat
user6079228
user6079228

Reputation: 97

sql date format order dd-mm-yyyy

I am using this code to format date with SQL:

convert(VARCHAR, DT, 20)

The output is: 

2016-01-01 20:40:12

But how do I get the day first.

I would like to get: dd-mm-yyyy: 

01-01-2016 20:40:12

When I look in this table: http://www.blackwasp.co.uk/sqldatetimeformats.aspx I do not see this formatting.

Thx!

Upvotes: 1

Views: 1116

Answers (2)

Thomas G
Thomas G

Reputation: 10216

For dd/mm/yyyy hh:mi:ss

  (CONVERT(varchar, DT, 103) + ' ' + CONVERT(varchar, DT, 108)) AS MyEuropeanDate

For dd-mm-yyyy hh:mi:ss

  (CONVERT(varchar, DT, 105) + ' ' + CONVERT(varchar, DT, 108)) AS MyEuropeanDate

And to understand it right for the next time, look at the official doc that Gordon wisely linked in the comments : https://msdn.microsoft.com/en-us/library/ms187928.aspx

Upvotes: 2

Fuzzy
Fuzzy

Reputation: 3810

Could not find a native tSQL function but you can do something like this:

    DECLARE @Date DATETIME = '20160101';

SELECT CASE
         WHEN LEN(CAST(DAY(@Date) AS VARCHAR)) = 1 THEN '0'+CAST(DAY(@Date) AS VARCHAR)
         ELSE CAST(DAY(@Date) AS VARCHAR)
      END
      +'-'+
      CASE
         WHEN LEN(CAST(MONTH(@Date) AS VARCHAR)) = 1 THEN '0'+CAST(MONTH(@Date) AS VARCHAR)
         ELSE CAST(MONTH(@Date) AS VARCHAR)
      END
      +'-'+
      CAST(YEAR(@Date) AS VARCHAR)
      +' '+
      SUBSTRING(CONVERT(VARCHAR, @Date, 114), 1, 8);

OR you can do this:

DECLARE @Date DATETIME = '20160101';

SELECT  (REPLACE(CONVERT(varchar, @Date, 103),'/','-') + ' ' + CONVERT(varchar, @Date, 108))

RESULT:

enter image description here

Upvotes: 0

Related Questions