Link to home
Start Free TrialLog in
Avatar of JoeSnyderJr
JoeSnyderJrFlag for United States of America

asked on

How to remove time from MSSQL datetime field

I have several tables with seconds minutes etc in datetime field. We want to truncate the field value to just yyyy-mm-dd. What select statement can we use to accomplish this?
ASKER CERTIFIED SOLUTION
Avatar of expertsoul
expertsoul
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JPJ78
JPJ78
Flag of Sweden image
This convert the datetime column to string acording to ISO8601 standard (yyyy-mm-ddThh:mi:ss.mmm) and truncates everything down to 10 characters (yyyy-mm-dd)

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Convert(varchar[10],myDateTimeCol,126)

Open in new window

Avatar of James Murrell
James Murrell
Flag of United Kingdom of Great Britain and Northern Ireland image
Cast(Left(GetDate, 12) as DateTime)
SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of expertsoul
expertsoul
Flag of United States of America image
Yes the convert to varchar is good only if you are trying to do display the date, if you want to store values to another table or you want to apply sorting on retrieved field, i dont think you wana use varchar.

The ways I suggested can be used if you want to keep field as datetime but ignore time so a date 2009-01-11 14:04:41.007 will be converted to 2009-01-11 00:00:00.000.


SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JoeSnyderJr
JoeSnyderJr
Flag of United States of America image

ASKER

Tested solutions offered and any one of these works to truncate datetime to date as requested

CAST((
STR( YEAR( GETDATE() ) ) + '/' +
STR( MONTH( GETDATE() ) ) + '/' +
STR( DAY( GETDATE() ) )
)AS DATETIME )

OR this

Cast(Left(GetDate, 12) as DateTime)

Thanks to all for solutions

OR these

select Convert(nvarchar(10),getdate(),20)
select Convert(nvarchar(10),getdate(),120)
select Convert(nvarchar(10),getdate(),21)
select Convert(nvarchar(10),getdate(),121)
select Convert(nvarchar(10),getdate(),126)
select Convert(nvarchar(10),getdate(),127)

OR this

dateadd( day, datediff( day, 0, getdate() ), 0 )