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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Date Difference

Author  Topic 

mahesh_bote
Constraint Violating Yak Guru

298 Posts
Posted - 2006-07-24 : 01:24:33
how to get date difference. how can i get the Month, Day, Year differences?

BMahesh

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2006-07-24 : 01:38:21
use datediff(). Look up Books OnLine for more information and syntax of datediff()

KH
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-07-24 : 03:07:57
Not sure its quite that easy, is it KH?

You'd need to find the number of years, and then subtract them to get the months, subtract that to then get the days?

Looks like a reasonable job for a function, although that can only return a single, composite, result

I'm not sure what the correct answer is for the interval 29-Feb-2000 to 28-Feb-2001 (I make it one year exactly!!)

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

CREATE FUNCTION dbo.kk_fn_UTIL_DateDiff
(
	@dtDate1	datetime,	-- Start/Early Date Value
	@dtDate2	datetime,	-- End/Later Date Value
	@strTemplate	varchar(8000)	-- Template for the output - e.g. "{YEAR} years, {MONTH} months and {DAY} days" 
)
RETURNS varchar(8000)
/* WITH ENCRYPTION */
AS
/*
 * kk_fn_UTIL_DateDiff	Difference of two dates, in the individual components
 *			NULL returned as NULL
 * 	SELECT	dbo.kk_fn_UTIL_DateDiff('20000101', GetDate())
 *
 * Returns:
 *
 * 	String, based on the template, describing the various date part differences
 *
 * HISTORY:
 *
 * 24-July-2006 KBM  Started
 */
BEGIN
DECLARE	@intYears	int,
	@intMonths	int,
	@intDays	int,
	@intHours	int,
	@intMinutes	int,
	@intSeconds	int

	SELECT	@intYears = DATEDIFF(Year, @dtDate1, @dtDate2),
		@dtDate1 = DATEADD(Year, @intYears, @dtDate1),
		@intMonths = DATEDIFF(Month, @dtDate1, @dtDate2),
		@dtDate1 = DATEADD(Month, @intMonths, @dtDate1),
		@intDays = DATEDIFF(Day, @dtDate1, @dtDate2),
		@dtDate1 = DATEADD(Day, @intDays, @dtDate1),
		@intHours = DATEDIFF(Hour, @dtDate1, @dtDate2),
		@dtDate1 = DATEADD(Hour, @intHours, @dtDate1),
		@intMinutes = DATEDIFF(Minute, @dtDate1, @dtDate2),
		@dtDate1 = DATEADD(Minute, @intMinutes, @dtDate1),
		@intSeconds = DATEDIFF(Second, @dtDate1, @dtDate2)
--		@dtDate1 = DATEADD(Month, @intSeconds, @dtDate1)

	SELECT	@strTemplate = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
				@strTemplate
				, '{YEAR}', CONVERT(varchar(20), @intYears))
				, '{MONTH}', CONVERT(varchar(20), @intMonths))
				, '{DAY}', CONVERT(varchar(20), @intDays))
				, '{HOUR}', CONVERT(varchar(20), @intHours))
				, '{MINUTE}', CONVERT(varchar(20), @intMinutes))
				, '{SECOND}', CONVERT(varchar(20), @intSeconds))
				, '{YEARS}', CASE WHEN @intYears = 1 THEN '' ELSE 's' END)
				, '{MONTHS}', CASE WHEN @intMonths = 1 THEN '' ELSE 's' END)
				, '{DAYS}', CASE WHEN @intDays = 1 THEN '' ELSE 's' END)
				, '{HOURS}', CASE WHEN @intHours = 1 THEN '' ELSE 's' END)
				, '{MINUTES}', CASE WHEN @intMinutes = 1 THEN '' ELSE 's' END)
				, '{SECONDS}', CASE WHEN @intSeconds = 1 THEN '' ELSE 's' END)
	RETURN @strTemplate
/** TEST RIG

SELECT	'01-Jan-2000 02-Jan-2000', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, '01-Jan-2000'), CONVERT(datetime, '02-Jan-2000'), '{YEAR} year{YEARS}, {MONTH} month{MONTHS} and {DAY} day{DAYS}')
SELECT	'01-Jan-2000 01-Feb-2000', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, '01-Jan-2000'), CONVERT(datetime, '01-Feb-2000'), '{YEAR} year{YEARS}, {MONTH} month{MONTHS} and {DAY} day{DAYS}')
SELECT	'01-Jan-2000 01-Jan-2001', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, '01-Jan-2000'), CONVERT(datetime, '01-Jan-2001'), '{YEAR} year{YEARS}, {MONTH} month{MONTHS} and {DAY} day{DAYS}')
SELECT	'01-Jan-2000 02-Mar-2003', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, '01-Jan-2000'), CONVERT(datetime, '02-Mar-2003'), '{YEAR} year{YEARS}, {MONTH} month{MONTHS} and {DAY} day{DAYS}')

SELECT	'01-Jan-2000 00:00:00 01-Jan-2000 00:00:01', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, '01-Jan-2000'), CONVERT(datetime, '01-Jan-2000 00:00:01'), '{YEAR} year{YEARS}, {MONTH} month{MONTHS}, {DAY} day{DAYS}, {HOUR} hour{HOURS}, {MINUTE} minute{MINUTES} and {SECOND} second{SECONDS}')
SELECT	'01-Jan-2000 00:00:00 01-Jan-2000 00:01:00', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, '01-Jan-2000'), CONVERT(datetime, '01-Jan-2000 00:01:00'), '{YEAR} year{YEARS}, {MONTH} month{MONTHS}, {DAY} day{DAYS}, {HOUR} hour{HOURS}, {MINUTE} minute{MINUTES} and {SECOND} second{SECONDS}')
SELECT	'01-Jan-2000 00:00:00 01-Jan-2000 01:00:00', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, '01-Jan-2000'), CONVERT(datetime, '01-Jan-2000 01:00:00'), '{YEAR} year{YEARS}, {MONTH} month{MONTHS}, {DAY} day{DAYS}, {HOUR} hour{HOURS}, {MINUTE} minute{MINUTES} and {SECOND} second{SECONDS}')
SELECT	'01-Jan-2000 00:00:00 01-Jan-2000 01:02:03', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, '01-Jan-2000'), CONVERT(datetime, '01-Jan-2000 01:02:03'), '{YEAR} year{YEARS}, {MONTH} month{MONTHS}, {DAY} day{DAYS}, {HOUR} hour{HOURS}, {MINUTE} minute{MINUTES} and {SECOND} second{SECONDS}')

SELECT	'29-Feb-2000 28-Feb-2001', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, '29-Feb-2000'), CONVERT(datetime, '28-Feb-2001'), '{YEAR} year{YEARS}, {MONTH} month{MONTHS} and {DAY} day{DAYS}')

SELECT	'01-Jan-2000 NULL', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, '01-Jan-2000'), CONVERT(datetime, NULL), '{YEAR} year{YEARS}, {MONTH} month{MONTHS} and {DAY} day{DAYS}')
SELECT	'NULL 01-Jan-2000', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, NULL), CONVERT(datetime, '01-Jan-2000'), '{YEAR} year{YEARS}, {MONTH} month{MONTHS} and {DAY} day{DAYS}')
SELECT	'NULL NULL', dbo.kk_fn_UTIL_DateDiff(CONVERT(datetime, NULL), CONVERT(datetime, NULL), '{YEAR} year{YEARS}, {MONTH} month{MONTHS} and {DAY} day{DAYS}')

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

Kristen
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2006-07-24 : 03:33:04
" Not sure its quite that easy, is it KH?"


BMahesh, are you looking for different of 2 dates in terms of year, month & day ?

Also refer to here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729

KH
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

298 Posts
Posted - 2006-07-24 : 04:07:10
thanx both of u. i wants to take the differences between two dates. "DateDiff" is enough for me

BMahesh
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2006-07-24 : 04:19:46
quote:
Originally posted by mahesh_bote

thanx both of u. i wants to take the differences between two dates. "DateDiff" is enough for me

BMahesh



KH
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-07-24 : 05:56:44
How come the answer doesn't sound anything like the question?!

Kristen
Go to Top of Page

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts
Posted - 2006-07-24 : 06:24:19
quote:
Originally posted by Kristen

How come the answer doesn't sound anything like the question?!

Kristen
I think it does, but it's a very nice function all the same Kristen


Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site