Generate a Start & End Date - 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 2000 Forums

Transact-SQL (2000)

Generate a Start & End Date

Author

Topic

hey001us
Posting Yak Master

185 Posts

Posted - 2007-08-15 : 21:49:09

I have given below the sample:

The join is : '2007-01-31'

I need out like below:

Startdate enddate
2007-03-31 00:00:00.000 2007-04-30 23:59:59.000
2007-04-30 00:00:00.000 2007-05-30 23:59:59.000
2007-05-31 00:00:00.000 2007-06-30 23:59:59.000
2007-06-30 00:00:00.000 2007-07-30 23:59:59.000
2007-08-31 00:00:00.000 2007-09-30 23:59:59.000

How do I generate?

hey

hey001us
Posting Yak Master

185 Posts

Posted - 2007-08-15 : 21:51:06

Sorry, the output will be:
2007-04-30 00:00:00.000 2007-05-30 23:59:59.000
2007-05-31 00:00:00.000 2007-06-30 23:59:59.000
2007-06-30 00:00:00.000 2007-07-30 23:59:59.000
2007-07-31 00:00:00.000 2007-08-30 23:59:59.000
2007-08-31 00:00:00.000 2007-09-30 23:59:59.000

hey

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-15 : 21:53:45

what is the rule ? How to generate the result required from the given date '2007-01-31' ?

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

hey001us
Posting Yak Master

185 Posts

Posted - 2007-08-15 : 21:58:13

i got transaction table but some customer dont have some days, but still i like to show zero value.
hence i can't join with transaction table. is there any way we can generate the dates?

hey

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-15 : 21:59:27

use this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

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

hey001us
Posting Yak Master

185 Posts

Posted - 2007-08-15 : 21:59:45

i need to show the result each month how much they used by given date. i need to show zero value if nothing

hey

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-15 : 22:02:50