Date Calculation - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Date Calculation

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-28 : 22:17:55

I have a table that looks like this :

ID FromDate EndDate Qty
-- -------- ------- ----
1 10/1/2007 12/10/2007 4
2 1/1/2007 2/31/2007 3
3 3/1/2006 5/20/2006 2

I want to create a table containing the following data.
The NewEndDate is the number of Month endings within the FromDate
and EndDates respectively.
Any help will be most welcome.

ID FromDate EndDate NewEndDate QTY Seq
--- --------- -------- ---------- ---- ---
1 10/1/2007 12/10/2007 Oct 2007 4 1
1 10/1/2007 12/10/2007 Nov 2007 4 2
1 10/1/2007 12/10/2007 Dec 2007 4 3
2 1/1/2007 2/31/2007 Jan 2007 3 1
2 1/1/2007 2/31/2007 Feb 2007 3 2
3 3/1/2006 5/20/2006 Mar 2007 2 1
3 3/1/2006 5/20/2006 Apr 2007 2 2
3 3/1/2006 5/20/2006 May 2007 2 3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-29 : 00:03:23

make use of the F_TABLE_DATE and CROSS APPLY to it
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

KH
[spoiler]Time is always against us[/spoiler]

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-29 : 09:03:31

Having a hard time trying to adapt the code written by khtan
to solve my problem.
Can somebody please help me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 09:08:17

I didn't know there were 31 days in february 2007...

E 12°55'05.25"
N 56°04'39.16"

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-29 : 09:13:37

Sorry Peso, February date it should read 2/28/2007.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 09:15:12

[code]-- Prepare sample data
DECLARE @Sample TABLE (ID TINYINT, FromDate SMALLDATETIME, EndDate SMALLDATETIME, Qty TINYINT)

SET DATEFORMAT MDY

INSERT @Sample
SELECT 1, '10/1/2007', '12/10/2007', 4 UNION ALL
SELECT 2, '1/1/2007', '2/28/2007', 3 UNION ALL
SELECT 3,' 3/1/2006', '5/20/2006', 2

-- Show the expeced output
SELECT s.ID,
s.FromDate,
s.EndDate,
STUFF(CONVERT(CHAR(12), DATEADD(MONTH, v.Number, s.FromDate), 107), 5, 4, '') AS NewEndDate,
s.Qty,
1 + v.Number AS Seq
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE v.Number <= DATEDIFF(MONTH, s.FromDate, s.EndDate)
ORDER BY s.ID,
v.Number[/code]

E 12°55'05.25"
N 56°04'39.16"

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-29 : 09:32:53

Wao !!, Peso you are the best ,you have solved my problem.

Now if I want to display the full date (NewDate) what modification should I make to the line :

STUFF(CONVERT(CHAR(12), DATEADD(MONTH, v.Number, s.FromDate), 107), 5, 4, '') AS NewEndDate.

Warm Regards.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 09:42:23

Full date what? Formatted how?

Books Online has an excellent explanation of the different styles for CONVERT function.

E 12°55'05.25"
N 56°04'39.16"

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-29 : 09:57:26

Thanks a million Peso !!!!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 10:19:07

Or this neat little trick?

-- Prepare sample data
DECLARE	@Sample TABLE (ID TINYINT, FromDate SMALLDATETIME, EndDate SMALLDATETIME, Qty TINYINT)

SET DATEFORMAT MDY

INSERT	@Sample
SELECT	1, '10/11/2007', '12/10/2007', 4 UNION ALL
SELECT	2, '1/7/2007', '2/28/2007', 3 UNION ALL
SELECT	3,' 3/3/2006', '5/20/2006', 2

-- Show the expeced output
SELECT		s.ID,
		s.FromDate,
		s.EndDate,
		DATEADD(MONTH, DATEDIFF(MONTH, '19000101', s.FromDate), '19000101') AS NewFromDate,
		DATEADD(MONTH, v.Number + DATEDIFF(MONTH, '18991231', s.FromDate), '18991231') AS NewEndDate,
		s.Qty,
		1 + v.Number AS Seq
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'p'
WHERE		v.Number <= DATEDIFF(MONTH, s.FromDate, s.EndDate)
ORDER BY	s.ID,
		v.Number

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 10:20:49

[code]ID FromDate EndDate NewFromDate NewEndDate Qty Seq
1 2007-10-11 2007-12-10 2007-10-01 2007-10-31 4 1
1 2007-10-11 2007-12-10 2007-10-01 2007-11-30 4 2
1 2007-10-11 2007-12-10 2007-10-01 2007-12-31 4 3
2 2007-01-07 2007-02-28 2007-01-01 2007-01-31 3 1
2 2007-01-07 2007-02-28 2007-01-01 2007-02-28 3 2
3 2006-03-03 2006-05-20 2006-03-01 2006-03-31 2 1
3 2006-03-03 2006-05-20 2006-03-01 2006-04-30 2 2
3 2006-03-03 2006-05-20 2006-03-01 2006-05-31 2 3[/code]

E 12°55'05.25"
N 56°04'39.16"

hey001us
Posting Yak Master

185 Posts

Posted - 2008-01-29 : 16:09:13

Peso I am admired your SQL

hey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 16:18:56

Thanks!

E 12°55'05.25"
N 56°04'39.16"

hey001us
Posting Yak Master

185 Posts

Posted - 2008-01-29 : 16:23:27

where i can find the documents for spt_values?

hey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 16:45:57

It is a system table in master database.
You can do with any other NUMBER table, ranging from 0 to {large number here}.
You can even create your own.

E 12°55'05.25"
N 56°04'39.16"

hey001us
Posting Yak Master

185 Posts

Posted - 2008-01-29 : 16:49:44

the Number upto 256 right. let say i want more than that. how do i ?

hey

hey001us
Posting Yak Master

185 Posts

Posted - 2008-01-29 : 16:54:39

I got it Thanks Peso.

hey

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-01-29 : 23:00:25

Peso, I love your trick,
Why the choice of '19000101' =1/1/19900
and '18991231' =12/31/1899

Can you please explain the thoery hehind this ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-30 : 02:57:45

To get the first day of current month and last day of current month.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-30 : 02:58:46

quote:
Originally posted by hey001us

the Number upto 256 right. let say i want more than that. how do i ?

252 months is 21 years!
I think that is sufficient for most cases...

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources