Menu
I have an sql DateTime (ms sql server) and want to extract the same date without the seconds: e.g. 2011-11-22 12:14:58.000 to become: 2011-11-22 12:14:00.000
How can I do this? I was thinking to use DATEADD in combination with DATEPART but seems very error prone (besides performance issues)
802
If you're using SQL-Server 2008+ , you can use the TIME type and just reset the seconds and just ignore them while selecting. If not, you can consider saving them as strings(extract the HH:MI) or integers(counting minutes after 00:00).
Given below are the two methods that we can use to remove milliseconds and seconds from datetime. METHOD 1 : In this method, we will use Convert function to convert date time to varchar and then remove the seconds and milliseconds from it and then convert it back to datetime.
SELECT * FROM table1 WHERE purchase_date = '03-JUL-16' ; SELECT * FROM table1 WHERE purchase_date like '03-JUL-16'; SELECT * FROM table1 WHERE purchase_date in ( '03-JUL-16' );
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, yourcolumn), 0) FROM yourtable This will be effective, if you don't want a slow conversion between datatypes.
178
For a solution that truncates using strings try this:
SELECT CAST(CONVERT(CHAR(16), GetDate(),20) AS datetime) CHAR(16) works only if our variable is converted to ODBC canonical format, as shown above by using 20 as the format specifier.
DECLARE @date DateTime = '2011 Nov 22 12:14:55'; SELECT CONVERT(Char(16), @date ,20) AS datetime Results:
| datetime | |------------------| | 2011-11-22 12:14 | Then you simply cast back to a DateTime type to continue using the value.
NOTE: This is only viable for data types that do not carry TimeZone info. Also type conversions to VarChar and back are usually LESS performant than using DateTime functions that use numeric operations internally.
Consider other solutions posted if performance is a concern or if you must retain timezone information.
25
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
