Getting "date" from a DateTime field in SQL Server

Question

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) from this value. How to do it?

Answer 1

If you are using SQL Server 2008, there is now a DATE data type. Makes it a lot more natural!

 SELECT CONVERT(Date, GETDATE()) 

Answer 2

it is called "datetime flooring", it does so in order to remove only time (this is the fastest method, faster than using the header formatting CONVERT () or CAST ()):

 DECLARE @datetime datetime; SET @datetime = '2008-09-17 12:56:53.430'; SELECT DATEADD(day,DATEDIFF(day,0,@datetime),0) 

CONCLUSION:

 ----------------------- 2008-09-17 00:00:00.000 (1 row(s) affected) 

here's how to do it for other parts of 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 

CONCLUSION:

 -------- ----------------------- 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) 

Answer 3

If I understand your question correctly,

select convert(varchar, creation_date , 103) as creation_date from tablename

Check out CAST and CONVERT

Answer 4

Here:

 SELECT creation_date FROM risks WHERE creation_date = GETDATE() 

This will return all creation_date values ​​stored in the risks table that exactly match what is returned by the GETDATE() function. I assume the creation_date data type is Date .

Answer 5

You just need to include create_date in the select clause as follows:

 select id, creation_date from risks where creation_date = getdate() 

Answer 6

You can always use the month / day / year functions to return it:

 declare @date datetime set @date = '1/1/10 12:00 PM' select cast(month(@date) as varchar) + '/' + cast(day(@date) as varchar) + '/' + cast(year(@date) as varchar) as theDate