Getting the 'date' out of a DateTime field in SQL Server
20,652
Solution 1
If you're using SQL Server 2008, there is now a DATE datatype. Makes it a lot more natural!
SELECT CONVERT(Date, GETDATE())
Solution 2
it is called "flooring a datetime", do it like this to remove just the time (this is the fastest method, faster than using CONVERT() or CAST() sting formatting):
DECLARE @datetime datetime;
SET @datetime = '2008-09-17 12:56:53.430';
SELECT DATEADD(day,DATEDIFF(day,0,@datetime),0)
OUTPUT:
-----------------------
2008-09-17 00:00:00.000
(1 row(s) affected)
here is how to do it for other parts of a datetime:
--Floor a datetime
DECLARE @datetime datetime;
SET @datetime = '2008-09-17 12:56:53.430';
SELECT '0 None', @datetime -- none 2008-09-17 12:56:53.430
UNION SELECT '1 Second',DATEADD(second,DATEDIFF(second,'2000-01-01',@datetime),'2000-01-01') -- Second: 2008-09-17 12:56:53.000
UNION SELECT '2 Minute',DATEADD(minute,DATEDIFF(minute,0,@datetime),0) -- Minute: 2008-09-17 12:56:00.000
UNION SELECT '3 Hour', DATEADD(hour,DATEDIFF(hour,0,@datetime),0) -- Hour: 2008-09-17 12:00:00.000
UNION SELECT '4 Day', DATEADD(day,DATEDIFF(day,0,@datetime),0) -- Day: 2008-09-17 00:00:00.000
UNION SELECT '5 Month', DATEADD(month,DATEDIFF(month,0,@datetime),0) -- Month: 2008-09-01 00:00:00.000
UNION SELECT '6 Year', DATEADD(year,DATEDIFF(year,0,@datetime),0) -- Year: 2008-01-01 00:00:00.000
ORDER BY 1
PRINT' '
PRINT 'Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor'
PRINT 'this always uses a date less than the given date, so there will be no arithmetic overflow'
SELECT '1 Second',DATEADD(second,DATEDIFF(second,DATEADD(day,DATEDIFF(day,0,@datetime),0)-1,@datetime),DATEADD(day,DATEDIFF(day,0,@datetime),0)-1) -- Second: 2008-09-17 12:56:53.000
OUTPUT:
-------- -----------------------
0 None 2008-09-17 12:56:53.430
1 Second 2008-09-17 12:56:53.000
2 Minute 2008-09-17 12:56:00.000
3 Hour 2008-09-17 12:00:00.000
4 Day 2008-09-17 00:00:00.000
5 Month 2008-09-01 00:00:00.000
6 Year 2008-01-01 00:00:00.000
(7 row(s) affected)
Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor
this always uses a date less than the given date, so there will be no arithmetic overflow
-------- -----------------------
1 Second 2008-09-17 12:56:53.000
(1 row(s) affected)
Solution 3
If i got your question right,
select convert(varchar, creation_date , 103) as creation_date from tablename
Look at CAST and CONVERT
Author by
thegunner
Updated on April 14, 2020Comments
-
thegunner about 4 years
I have a date column where the date is displayed in the format
2009-11-18 10:55:28.370
.I just want to get the date (not time) out of that value. How do I do that?
-
thegunner about 14 yearsbut this won't return the creation_date values that are today's date. Isn't that what's done witn creation_date = getdate()
-
Oded about 14 years@thegunner - You did not specify that in your question. I was supposed to guess?
-
thegunner about 14 yearstried it doens't work....must have to convert somewhere select creation_date from WEB_RISK_RISK where CREATION_DATE=GETDATE(). p.s. there is a record which has been rected today.
-
thegunner about 14 yearsok answer is: select creation_date from WEB_RISK_RISK where CREATION_DATE = convert(date,GETDATE()) - thanks for the tip.