Get DATE from DATETIME field

Question

how can i get the date only from the datetime field

datetimefield
2010-07-14 12:48:30.000 
2010-08-11 18:50:30.000
2010-07-07 19:24:30.000 
2010-08-05 17:27:30.000

Answer 1

If you use 2008+

CONVERT(DATE, datetimefield)

 

Answer 2

 

select convert(char(10),datetimefield,121)

 

  

---

Best regards

Answer 3

Hi Sam

 

You can convert your datetime as the varchar and then extract the first 10 charecters from it.

see the below code

select left(convert(varchar(30),datetimefield,20),10)

---

Nothing is Permanent... even Knowledge....
My Blog

Answer 4

can this be kept in datetime datatype because i need use the date within SSRS 2005 on a parameter selection

Answer 5

Please be careful when cast to string.

Here a excellent article from Tibor about it:

http://www.karaszi.com/SQLServer/info_datetime.asp

Answer 6

 

SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', datetimefield), '19000101')

 

  

---

Best regards

Answer 7

if you are using SQL Server 2005, only date or only time data types are not there. even if you extract the date some how and store it in the datetime filed it will be converted again as DD-MMM-YYYY HH:MM:SS format....

 

as per your requirement if your SSRS report have to fetch only the date from the datetime field. Then you can use the simple convert statement from SQL Server which will give only the date from the datetime, (convert it as a varchar)...

or if you want to store the extracted date in a date type filed only the date should be stored. You have to create a custom type.

Please follow the blog post from Jeff for this custom type creation

http://weblogs.sqlteam.com/jeffs/archive/2007/10/31/sql-server-2005-date-time-only-data-types.aspx

 

---

Nothing is Permanent... even Knowledge....
My Blog