Menu
Currently sql returns the date as 2013-07-01 00:00:00.000. I want only the date part in mm/dd/yyyy format so that when I export it to excel I can still sort based on ascending or descending order. I tried varchar but it doesnt get sorted in excel.
693
In SQL Server 2008 or later, you can easily do this by casting/converting the datetime value to the datatype DATE. However, in earlier versions of SQL Server the DATE type is not available.
SQL Server - How to return Date Only from GETDATE Database Specific• 13-Apr-2020 The basic query that returns the date and time for SQL Server is SELECT getdate(); This query will return the current date & time of your local machine.
You can simply use CAST and get Datetime column value date part only using SQL Server DECLARE @LastChangeDate as datetime --create datetime variable SET @LastChangeDate = GetDate () Select @LastChangeDate --before cast SELECT CAST (@LastChangeDate AS DATE) -- after cast That's it.
The string can also convert into datetime and time value format. If the input string has more characters than the format string, the extra characters in the input string will be ignored and will be initialized to the default value. For example, to_date (‘10.02.2007 10:12:12, ‘DD.MM.YYYY’) will convert the date to 10.02.2007 00.00.00.
You can convert to DATE, but Excel might display those as numbers, not sure.
SELECT CONVERT(DATE, col) FROM dbo.table;
Otherwise you can use a specific style, e.g.
-- yyyy-mm-dd - standard, unambiguous format
SELECT CONVERT(CHAR(10), col, 120) FROM dbo.table;
Or
-- mm/dd/yyyy - ambiguous, regional format
SELECT CONVERT(CHAR(10), col, 101) FROM dbo.table;
To format as mm/dd/yyyy, convert to VARCHAR using format 101;
SELECT CONVERT(VARCHAR, GETDATE(), 101);
> 08/01/2013
An SQLfiddle.
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
