SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Convert DateTime to Date using Rounding UDF		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22191 Posts
Posted - 02/25/2006 :  01:46:09  Show Profile  Reply with Quote
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
Edited by - Kristen on 02/25/2006 02:16:48

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 02/25/2006 :  11:08:15  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 02/25/2006 :  12:09:49  Show Profile  Reply with Quote
By Jiminy you're right! That's a handy side effect I hadn't expected, thanks.

Kristen
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 02/25/2006 :  18:22:04  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 02/27/2006 :  09:35:58  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Good solution to Mostly Asked Question

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000