Date Range

Hi Everybody,
I need to return a values in a specific format
I have a Table which has Start_date and End_Date as
Start_Date End_date
'1/28/2006' and '1/31/2006'
I want to return values as
Start_date End_Date
1/28/2006 1/29/2006
1/29/2006 1/30/2006
1/30/2006 1/31/2006
How do I do this? I dont care If it is a function or a sp or a Select
statement.
Any help is sincerely appreciated.
TIA,
SunnyYou want to use ISO-8601 formats so your code will port. Next,
formatting in done in the front end and not in the database.|||Sunny
Inserting dates into SQL Server you'll have to use YYYYMMDD format.
If yopu want to display dates on the client , use FORMAT(VB6) for instance.
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||If I am understanding what you want correctly, you want a row for each day
between the start and end dates in your table.
For this you can use a calendar table as the example below shows:
CREATE TABLE Calendar
(
DateValue DateTime
)
GO
CREATE TABLE MyDates
(
ID INT NOT NULL PRIMARY KEY,
StartDate DateTime,
EndDate DateTime
)
GO
-- FIll The calendar Table
DECLARE @.TempDate DateTime
SET @.TempDate = '2005-11-01'
WHILE @.TempDate <= '2006-06-30'
BEGIN
INSERT INTO Calendar(DateValue)
VALUES (@.TempDate)
SET @.TempDate = DATEADD(d,1, @.TempDate)
END
-- Put data in MyDates
INSERT INTO MyDates (ID, StartDate, EndDate)
SELECT 1,'2005-12-28','2005-12-31'
UNION
SELECT 2,'2005-12-28','2006-01-05'
-- Now select the date ranges for a single row
SELECT DateValue AS StartDate,
DATEADD(d,1,DateValue) AS EndDate
FROM Calendar
INNER JOIN MyDates
ON Calendar.DateValue >= MyDates.StartDate
AND Calendar.DateValue < MyDates.EndDate
WHERE MyDates.ID = 1
-- Now demonstrate that it works using multiple rows
SELECT MyDates.ID AS ID,
DateValue AS StartDate,
DATEADD(d,1,DateValue) AS EndDate
FROM Calendar
INNER JOIN MyDates
ON Calendar.DateValue >= MyDates.StartDate
AND Calendar.DateValue < MyDates.EndDate
WHERE MyDates.ID IN (1,2)
DROP TABLE Calendar
GO
DROP TABLE MyDates
GO
I do agree with the other posts that you should use the ISO date standard
(as shown in my example) and then format the dates on the client to the
user's desired display format.
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||Hi Sunny,
You might want to use DD MMM YYYY format as in the UK there are not 28
months in a year and we use the format DD/MM/YYYY, alternatively use
YYYYMMDD.
Anyway, this SQL using a sequence table will do what you require...
declare @.seqtable table (
seqnumber tinyint not null
)
insert @.seqtable ( seqnumber ) values ( 1 )
insert @.seqtable ( seqnumber ) values ( 2 )
insert @.seqtable ( seqnumber ) values ( 3 )
insert @.seqtable ( seqnumber ) values ( 4 )
insert @.seqtable ( seqnumber ) values ( 5 )
insert @.seqtable ( seqnumber ) values ( 6 )
insert @.seqtable ( seqnumber ) values ( 7 )
insert @.seqtable ( seqnumber ) values ( 8 )
select start_date = dateadd( day, seqnumber, '28 jan 2006' ),
end_date = dateadd( day, seqnumber+1, '28 jan 2006' )
from @.seqtable
order by start_date
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||What formatting?
The poster wants to expand a date range into a range of rows, 1 day per row.
Are we now not to do these simplest of things in the RDBMS to keep things
simple sweet?
Why on earth would you want to go to the efforts of doing this in a 3gl when
its such a simple task using SQL?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138671987.369279.135430@.g49g2000cwa.googlegroups.com...
> You want to use ISO-8601 formats so your code will port. Next,
> formatting in done in the front end and not in the database.
>|||Sunny,
will it help you?
SELECT DATEADD(D, S.seq - 1, P.beg_date) AS today,
DATEADD(D, S.seq, P.beg_date) AS tomorrow
FROM (SELECT hundred * 100 + ten * 10 + unit + 1
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Hundreds(hundred)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Tens(ten)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Units(unit)) AS S(seq),
(SELECT CAST('20060128' AS DATETIME),
CAST('20060131' AS DATETIME)
UNION ALL
SELECT CAST('20060225' AS DATETIME),
CAST('20060303' AS DATETIME)
UNION ALL
SELECT CAST('20051225' AS DATETIME),
CAST('20060103' AS DATETIME)) AS P(beg_date, fin_date)
WHERE S.seq <= DATEDIFF(D, P.beg_date, P.fin_date);
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)