List of days - 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)

List of days

Author

Topic

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-17 : 13:43:30

I want to have a list of a period day such as from
'2012-01-01', '2012-01-12'
IS ther a function 'explodeDates' in SQL?

select
ExplodeDates('2012-01-01', '2012-01-08');

My expected result:
2012-01-01
2012-01-02
2012-01-03
2012-01-04
2012-01-05
2012-01-06
2012-01-07
2012-01-08

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-17 : 13:48:03

with cte as
(
select d = converrt(datetime,'20120101')
union all
select d+1 from cte where d < '20120108'
)
select * frrom cte

You might want to create a calendar table if you are dealing with dates and join to that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-17 : 13:50:54

But my period time are not a sequence time

IT can be like this
20120108
20120110
20120207
20120511

--------------------------------

quote:
Originally posted by nigelrivett

with cte as
(
select d = converrt(datetime,'20120101')
union all
select d+1 from cte where d < '20120108'
)
select * frrom cte

You might want to create a calendar table if you are dealing with dates and join to that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-17 : 14:37:11

WHich is it?

quote:
Originally posted by JadeV

I want to have a list of a period day such as from
'2012-01-01', '2012-01-12'
IS ther a function 'explodeDates' in SQL?

select
ExplodeDates('2012-01-01', '2012-01-08');

My expected result:
2012-01-01
2012-01-02
2012-01-03
2012-01-04
2012-01-05
2012-01-06
2012-01-07
2012-01-08

quote:
Originally posted by JadeV

But my period time are not a sequence time

IT can be like this
20120108
20120110
20120207
20120511

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-17 : 14:39:57

yeah, I got it,
Just use " union all" and got it
Thanks

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-07-17 : 15:08:22

[quote]Originally posted by JadeV

But
I want a list of a day between 2 days

select Datediff("dd",StartDate, EndDate)
from schedule
where StartDate between '2012-01-01' and '2012-01-10'

The expect result is

StartDate
2012-01-01

EndDate
2012-01-05

DateDiff
4

Duration_day
2012-01-01,2012-01-02, 2012-01-03, 2012-01-04, 2012-01-05

Anyone know how to do this, please let's me know. THanks !

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-07-20 : 18:32:15

I'm either really lost (always a possibility) or Nigel already gave you the answer. You just need to enter the begin and end dates into the scriptlet he provided.

=================================================
Show me a sane man and I will cure him for you. -Carl Jung, psychiatrist (1875-1961)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources