RE: Separating Date and Time in T-SQL

re: RE: Separating Date and Time in T-SQL

Muncho — Thu, 08 Oct 2009 14:46:59 GMT

You can use cast(... as int) instead of "ROUND":

cast(cast(cast(@Time as float) as int) as datetime)

You can even omit cast(... as datetime) if you are assigning to a "DATETIME" variable or column. This is because you can assign an integer to a datetime and the value is considered the number of days since 1900-01-01.

re: RE: Separating Date and Time in T-SQL

Michael Bartlett — Mon, 18 Aug 2008 19:02:47 GMT

Great solution! Thanks.

re: RE: Separating Date and Time in T-SQL

david — Fri, 23 May 2008 22:46:10 GMT

If you only need the date part of a datetime variable discarding hours, minutes, seconds a better solution is available here

www.ugmfree.it/TipsTsql.aspx

DECLARE @Today datetime

SELECT @Today = CONVERT(varchar,getdate(),112)

re: RE: Separating Date and Time in T-SQL

Luciano Evaristo Guerche — Wed, 05 May 2004 12:44:00 GMT

Dear Denny,

I guess the following link may help you somehow:

http://weblogs.asp.net/guerchele/archive/2004/02/10/70767.aspx

re: RE: Separating Date and Time in T-SQL

denny — Tue, 04 May 2004 21:31:00 GMT

Very slick....

somewhere I saw a snipet one time that would take a row of values and make it output as a set of rows...

U used that to make a drop-down-combo in html with one sql call to build the whole list.

/me wonders if I can find that code now?

re: RE: Separating Date and Time in T-SQL

Luciano Evaristo Guerche — Tue, 04 May 2004 21:08:00 GMT

Dear Jerry,

Thanks for your comments on the code. As you noted on time, ROUND may also TRUNCATE. I guess my code works properly, at least it worked when I tested it last time. Anyway, I am happy there are people out there watching my scarce postings and spending time commenting on it...

re: RE: Separating Date and Time in T-SQL

Jerry Pisk — Tue, 04 May 2004 20:54:00 GMT

And I look like an idiot, ROUND(value, 0, 1) will truncate, not ROUND :)

re: RE: Separating Date and Time in T-SQL

Jerry Pisk — Tue, 04 May 2004 20:52:00 GMT

Your function do not get the date part of a datetime value. The problem is that you're rounding the value instead of stripping the time value and just keeping the integer part:

CREATE FUNCTION [dbo].[up_date_value] (@date datetime) RETURNS datetime AS

RETURN CAST(FLOOR(CAST(@date AS float)) AS datetime)

Your function will return the next day for values in the afternoon. Mine function would return invalid values for negative dates but SQL server doesn't support those. If it did all we'd have to do is to add an IF there and use CEILING for negative dates.

RE: Separating Date and Time in T-SQL

TrackBack — Tue, 04 May 2004 17:15:00 GMT