SQLTeam.com | Weblogs | Forums

Date format 2


sorry, i forget category

I have a datetime value

I want to extract the data without seconds


Mydate = '2015-05-02 12: 40: 19,000'

I want to read only

Mydate = '2015-05-02 12:40'
What is the format
Thank you

SELECT CONVERT(VARCHAR(16), Mydate, 120) mydate


SELECT FORMAT(Mydate, 'yyyy-MM-dd HH:mm') Mydate

thanks sz1

I wonder if MIN and MAX walk on the formatted value

  SELECT MIN(FORMAT(Mydate, 'yyyy-MM-dd HH:mm')) Mydate -- format first date
  FROM Table1

  SELECT MAX(FORMAT(Mydate, 'yyyy-MM-dd HH:mm')) Mydate -- format last date
  FROM Table1

I STRONGLY recommend that you avoid FORMAT like the plague. It is 44 times slower than CONVERT.

In T-SQL, this will do the job just fine and with good performance.
SELECT CONVERT(CHAR(16),GETDATE(),120); --(as sz1 posted above)

You should apply MAX() or MIN() first on the date column before using convert()

Firstly, in your case, as the formatting is YYYY-MM-DD makes no different on the result. But for MDY or DMY the result will be wrong.

Secondly, the performance will be bad if you apply CONVERT() first.