You need to tell SQL Server it's a date; otherwise, it just sees a string, and ignores the style number since it's not relevant for a string. As Steve Kass pointed out, the code is only truly portable if you protect the incoming string from incorrect regional- or language-based translations (such as d/m/y - which could lead to an error or, even worse, the wrong data). I've updated the code to interpret the string as m/d/y regardless of locale, but if you're on SQL Server 2012 you could also use PARSE() as in his example (or TRY_PARSE() if you want to essentially ignore invalid dates).
And if you want the time attached including milliseconds, you need to allow more than 10 characters, and a style that supports milliseconds.
SELECT CONVERT(CHAR(23),CONVERT(DATETIME,'7/19/2013',101),121);
Result:
2013-07-19 00:00:00.000
If you don't care about milliseconds, you can use style 120 instead:
SELECT CONVERT(CHAR(19),CONVERT(DATETIME,'7/19/2013',101),120);
And if you don't care about seconds, you can truncate earlier:
SELECT CONVERT(CHAR(16),CONVERT(DATETIME,'7/19/2013',101),120);
You need to tell SQL Server it's a date; otherwise, it just sees a string, and ignores the style number since it's not relevant for a string. As Steve Kass pointed out, the code is only truly portable if you protect the incoming string from incorrect regional- or language-based translations (such as d/m/y - which could lead to an error or, even worse, the wrong data). I've updated the code to interpret the string as m/d/y regardless of locale, but if you're on SQL Server 2012 you could also use PARSE() as in his example (or TRY_PARSE() if you want to essentially ignore invalid dates).
And if you want the time attached including milliseconds, you need to allow more than 10 characters, and a style that supports milliseconds.
SELECT CONVERT(CHAR(23),CONVERT(DATETIME,'7/19/2013',101),121);
Result:
2013-07-19 00:00:00.000
If you don't care about milliseconds, you can use style 120 instead:
SELECT CONVERT(CHAR(19),CONVERT(DATETIME,'7/19/2013',101),120);
And if you don't care about seconds, you can truncate earlier:
SELECT CONVERT(CHAR(16),CONVERT(DATETIME,'7/19/2013',101),120);
Note that Aaron's solution will fail if the server is localized to a language with DMY as the date format. This is because the inner CONVERT in Aaron's example will incorporate the server locale, which may not be what you expect.
To make this bulletproof (assuming the source of the string doesn't automatically re-localize the format), convert the string with PARSE (requires SQL Server 2012 or later).
SET LANGUAGE English
SELECT CONVERT(CHAR(23),TRY_CONVERT(DATETIME,'7/19/2013'),121);
SELECT CONVERT(CHAR(23),PARSE('7/19/2013' AS DATETIME USING 'en-US'),121);
SET LANGUAGE Français
SELECT CONVERT(CHAR(23),TRY_CONVERT(DATETIME,'7/19/2013'),121);
SELECT CONVERT(CHAR(23),PARSE('7/19/2013' AS DATETIME USING 'en-US'),121);
SQL accepts this format for datetime yyyy-MM-dd HH:mm:ss and I am getting MM/dd/yyyy HH:mm:ss format
sql server - Date format in dd/MM/yyyy hh:mm:ss - Stack Overflow
Date Format (mm/dd/yyyy hh:mm), no seconds or milliseconds – SQLServerCentral Forums
Converting yyyy/mm/dd hh:mm:ss – SQLServerCentral Forums
Supported by SQL Server 2005 and later versions
SELECT CONVERT(VARCHAR(10), GETDATE(), 101)
+ ' ' + CONVERT(VARCHAR(8), GETDATE(), 108)
* See Microsoft's documentation to understand what the 101 and 108 style codes above mean.
Supported by SQL Server 2012 and later versions
SELECT FORMAT(GETDATE() , 'MM/dd/yyyy HH:mm:ss')
Result
Both of the above methods will return:
10/16/2013 17:00:20
Try below:
SELECT CONVERT(VARCHAR(20), GETDATE(), 101)
Since you're on SQL 2012 the format function should work:
declare @date datetime = '2014-09-26 11:04:54'
select FORMAT(@date,'MM/dd/yyyy hh:mm:s tt')
result: 09/26/2014 11:04:54 AM
In your case it would be:
Select FORMAT(EntryDate,'MM/dd/yyyy hh:mm:s tt')
From DB1
DECLARE @Date_Value DATETIME = GETDATE();
SELECT CONVERT(VARCHAR(10), @Date_Value, 101) + ' '
+ LTRIM(RIGHT(CONVERT(CHAR(20), @Date_Value, 22), 11))
RESULT: 09/26/2014 5:25:53 PM
Your Query
SELECT CONVERT(VARCHAR(10), EntryDate, 101) + ' '
+ LTRIM(RIGHT(CONVERT(CHAR(20), EntryDate, 22), 11))
From DB1
See http://msdn.microsoft.com/en-us/library/ms187928.aspx
You can concatenate it:
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) + ' ' + CONVERT(VARCHAR(8), GETDATE(), 108)
A quick way to do it in sql server 2012 is as follows:
SELECT FORMAT(GETDATE() , 'dd/MM/yyyy HH:mm:ss')
SELECT CONVERT(char(10), GetDate(),126)
Limiting the size of the varchar chops of the hour portion that you don't want.
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02
SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) -- dd mon yyyy
SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy
SELECT convert(varchar, getdate(), 108) -- hh:mm:ss
SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) -- yyyymmdd
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm
SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm