Convert DateTime to Date using Rounding UDF

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Convert DateTime to Date using Rounding UDF

Author

Topic

Kristen
Test

22859 Posts

Posted - 2006-02-25 : 01:46:09

This UDF will strip off the time portion of a DateTime. It can give you either midnight last night, or midnight tonight.

Lets say you have two datetime values @dateStart and @dateEnd, and you want to select records between these dates (excluding any time portion), then you would do:


SELECT * 
FROM MyTable 
WHERE MyDateTimeColumn >= dbo.kk_fn_UTIL_DateRound(@dateStart, 0)
  AND MyDateTimeColumn < dbo.kk_fn_UTIL_DateRound(@dateEnd, 1)

If you want to display dates, without the time, then do:


SELECT dbo.kk_fn_UTIL_DateRound(MyDateColumn, 0) AS [My Date]
FROM MyTable


--
PRINT 'Create function kk_fn_UTIL_DateRound'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_DateRound]') AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.kk_fn_UTIL_DateRound
GO

CREATE FUNCTION dbo.kk_fn_UTIL_DateRound
(
	@dtDate		datetime,	-- Date Value to adjust
	@intRound	int		-- 0=Round down [Midnight last night], 1=Round up [Midnight tonight]
)
RETURNS datetime
/* WITH ENCRYPTION */
AS
/*
 * kk_fn_UTIL_DateRound	Convert date to midnight tonight
 *			For a "limit" date of '01-Jan-2000' the test needs to be 
 *				MyColumn < '02-Jan-2000' 
 *			to catch any item with a time during 1st Jan
 *
 *	SELECT	dbo.kk_fn_UTIL_DateRound(GetDate(), 0)	-- Midnight last night
 *	SELECT	dbo.kk_fn_UTIL_DateRound(GetDate(), 1)	-- Midnight tonight
 *
 * Returns:
 *
 * 	datetime
 *
 * HISTORY:
 *
 * 28-Jul-2005 KBM  Started
 */
BEGIN

	SELECT	@dtDate = DATEADD(Day, DATEDIFF(Day, 0, @dtDate)+@intRound, 0)

	RETURN @dtDate
/** TEST RIG

SELECT	'01-Jan-2000', dbo.kk_fn_UTIL_DateRound('01-Jan-2000', 0)
SELECT	'01-Jan-2000', dbo.kk_fn_UTIL_DateRound('01-Jan-2000', 1)

SELECT	'01-Jan-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('01-Jan-2000 01:02:03', 0)
SELECT	'01-Jan-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('01-Jan-2000 01:02:03', 1)

SELECT	'28-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('28-Feb-2000 01:02:03', 0)
SELECT	'28-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('28-Feb-2000 01:02:03', 1)

SELECT	'29-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('29-Feb-2000 01:02:03', 0)
SELECT	'29-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('29-Feb-2000 01:02:03', 1)

**/
--==================== kk_fn_UTIL_DateRound ====================--
END
GO
PRINT 'Create function kk_fn_UTIL_DateRound DONE'
GO
--

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-25 : 11:08:15

It looks like you can use the function to return midnight for any day you want, relative to today.


select
	Test_Date = '01-Jan-2000 01:02:03', 
	Offset,
	Result_Date =
		dbo.kk_fn_UTIL_DateRound('01-Jan-2000 01:02:03', a.Offset)
from
	(
	select Offset =    0  union all
	select Offset =    1  union all
	select Offset =   -1  union all
	select Offset = -444  union all
	select Offset =  225  union all
	select Offset =  365  union all
	select Offset =  366  union all
	select Offset = -365  union all
	select Offset = -366
	) a

Results:


Test_Date            Offset      Result_Date
-------------------- ----------- ----------------------- 
01-Jan-2000 01:02:03 0           2000-01-01 00:00:00.000
01-Jan-2000 01:02:03 1           2000-01-02 00:00:00.000
01-Jan-2000 01:02:03 -1          1999-12-31 00:00:00.000
01-Jan-2000 01:02:03 -444        1998-10-14 00:00:00.000
01-Jan-2000 01:02:03 225         2000-08-13 00:00:00.000
01-Jan-2000 01:02:03 365         2000-12-31 00:00:00.000
01-Jan-2000 01:02:03 366         2001-01-01 00:00:00.000
01-Jan-2000 01:02:03 -365        1999-01-01 00:00:00.000
01-Jan-2000 01:02:03 -366        1998-12-31 00:00:00.000

(9 row(s) affected)

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-02-25 : 12:09:49

By Jiminy you're right! That's a handy side effect I hadn't expected, thanks.

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-25 : 18:22:04

quote:
Originally posted by Kristen

By Jiminy you're right! That's a handy side effect I hadn't expected, thanks.

Kristen

Oh, all the while i thought this was your intention.

Maybe you should change the name of the function to reflect it's true capability.

Good Work. This is a nice script.

----------------------------------
'KH'
It is inevitable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-27 : 09:35:58

Good solution to Mostly Asked Question

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources