date portion of datetime 1

[password99]

how to get just the date portion of datetime value?

so if values in DB are:
12/31/2004 11:25:39
12/31/2004 11:21:19
12/31/2004 00:00:00

then I want either 12/31/2004 00:00:00 or 12/31/2004 returned

 

[Kris11]

Here you go

SELECT CONVERT (DATETIME, CONVERT (VARCHAR, getDATE(), 101))

-Kris

 

[mikeadn]

Just type this in your select statement:

LEFT(Tbl_Name, 10)

This returns 10 chars starting from the left. Be careful on how the date is being stored. Using 10 is assuming that you have leading zeros in your date field.. 01/02/2004.
good luck

Mike Cormier
May God bless everything you do...

 

[password99]

Thanks

is it possible to convert it into date as the converted value will be used in SELECT statement/View and it requires it to be a date/datetime. what I am interested in is date without the time portion .i.e.
12/31/2004 00:00:00 or 12/31/2004

 

[bdreed35]

Select field1,
CONVERT (DATETIME, CONVERT (VARCHAR(10), getDATE(), 101)),
field3
from SomeTable

just put it into your select statement.

~Brian

 

[r937]

mike, dates in sql server are not stored as mm/dd/yyy strings with or without leading zeroes, dates are stored as whole integers, representing the number of days since january 1 1753

rudy

http://r937.com/

SQL Consulting