May 9, 2012 at 9:58 am
HI.
I need a date table that has all the dates from 2000 till today. the Table should look like as below.
DateSkDateDay
200001011/1/20001
200001021/2/20002
200001031/3/20003
200001041/4/20004
200001051/5/20005
200001061/6/20006
200001071/7/20007
200001081/8/20008
200001091/9/20009
how can i write sql query ?
May 9, 2012 at 10:06 am
I've posted my version of a Calendar table a few times in some other posts; take a look at my version, and you can decide whether you want to use it as a model to create your own, or jsut adapt to using this one:
it creates a table form the 01/01/1900 to getdate + 100 years in the future.
once you have a Calendar table, you'll see lots of ways to use it.
TallyCalendar_Complete_With_DST.txt
Lowell
May 9, 2012 at 10:09 am
Oh Ok.
Thank you Lowell.
Thanks a lot.
May 9, 2012 at 10:11 am
That will give you exactly what you asked for...
select CONVERT(VARCHAR, [Date], 112) AS DateSk -- ISO
,[Date]
,rn + 1 AS [Day]
from
(
select rn,cast(dateadd(DAY,rn,'20000101') as DATE) as [Date]
from (
select TOP (DATEDIFF(day,'20000101', GETDATE()) + 1)
ROW_NUMBER() over (order by (select null)) - 1 rn
from sys.columns s1, sys.columns s2, sys.columns s3
) q
P.S. I hope you have enough records in sys.columns to get tripled Cartesian product of it to produce 4513 rows...
May 9, 2012 at 10:15 am
patelronaks1983 (5/9/2012)
HI.I need a date table that has all the dates from 2000 till today. the Table should look like as below.
DateSkDateDay
200001011/1/20001
200001021/2/20002
200001031/3/20003
200001041/4/20004
200001051/5/20005
200001061/6/20006
200001071/7/20007
200001081/8/20008
200001091/9/20009
how can i write sql query ?
SELECT DATEADD(D,N,'2000-01-01') AS [datetime],
CONVERT(VARCHAR(8),DATEADD(D,N,'2000-01-01'),112) AS datesk,
CONVERT(VARCHAR(8),DATEADD(D,N,'2000-01-01'),103) AS [date],
N+1 AS [day]
FROM (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3)a(N)
WHERE N <= DATEDIFF(D,'2000-01-01',GETDATE())
--edit-- Well, I type too slow again 😛
May 9, 2012 at 10:18 am
hi
thanks a lot for your help.. i rally appreciate it.
THanks again...
May 9, 2012 at 10:19 am
HI there.
Thanks a lot for your effort. This is what i am looking for...
Thank you very much
May 9, 2012 at 10:25 am
hi SSCommitted.
I am new in SQL server. i am not much familiar with code. can u explain me the code please ?
Thanks,
Ronak
May 10, 2012 at 3:38 am
patelronaks1983 (5/9/2012)
hi SSCommitted.I am new in SQL server. i am not much familiar with code. can u explain me the code please ?
Thanks,
Ronak
"SSCommitted" is a title, not a username. Who were you addressing?
Well, not important I guess. Explanations!!
First, Eugene's code (formatted, but otherwise the same).
SELECT
CONVERT(VARCHAR, [Date], 112) AS DateSk, --Convert the date to a new format. On my system this is in the format yyyymmdd.
[Date], --Date from the subquery "qq". On my system this is in the format yyyy-mm-dd.
rn + 1 AS [Day] --Row number from the subquery "qq" + 1. This is to give you your "day" figure.
FROM (SELECT
rn, --Row number from the subquery "q". This will be between 0 and the total number of days between the "start" date and today.
cast(dateadd(DAY, rn, '20000101') AS DATE) AS [Date] --Adds the row number from the subquery "q" to the "start" date.
FROM (SELECT TOP (DATEDIFF(day, '20000101', GETDATE()) + 1) --TOP number of days between the "start" date and tomorrow.
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 rn --Numbers between 0 and the total number of days between the "start" date and today.
FROM sys.columns s1, sys.columns s2, sys.columns s3 --Cartesian product of sys.columns table three times. This is to generate a lot
--of data because we need a sequence between 0 and the total number of days between
--the "start" date and tomorrow.
) q
) qq;
Now mind (I've corrected it because I spotted the data type I had used for "date" was too small to fit the format).
SELECT
DATEADD(D,N,'2000-01-01') AS [datetime], --Adds the row number from the subquery "a" to the "start" date.
--On my system, this is in the format yyyy-mm-dd hh:mm:ss.xxx.
CONVERT(VARCHAR(8),DATEADD(D,N,'2000-01-01'),112) AS datesk, --Adds the row number from the subquery "a" to the "start" date.
--This is then converted to the new format, which on my system looks
--like yyyymmdd.
CONVERT(VARCHAR(10),DATEADD(D,N,'2000-01-01'),103) AS [date], --Adds the row number from the subquery "a" to the "start" date.
--This is then converted to the new format, which on my system looks
--like dd/mm/yyyy.
N+1 AS [day] --Row number from the subquery "a" + 1. This is to give you your "day" figure.
FROM (SELECT 0 UNION ALL --This provides a 0 base for the numbers.
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) --Numbers between 1 and X.
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2,
master.dbo.syscolumns sc3 --Cartesian product of sys.columns table three times. This is to generate a lot
--of data because we need a sequence between 0 and the total number of days between
--the "start" date and tomorrow.
)a(N)
WHERE N <= DATEDIFF(D,'2000-01-01',GETDATE()) -- This is to limit the subquery "a" to the number of days between the "start" date and today.
;
--edit-- Added code tags.
May 10, 2012 at 4:17 am
Sorry Cadavre, I need to correct the comments for my code.
SELECT CONVERT(VARCHAR, [Date], 112) AS DateSk --Convert the date to string in ISO format. (On any system it will be yyyymmdd).
,[Date] --Date from the subquery "qq". It's of DATE datatype. In SSMS it will be displayed based on your locale.
,rn + 1 AS [Day] --Row number from the subquery "qq" + 1. This is to give you your "day" figure.
FROM (SELECT
rn, --Row number from the subquery "q". This will be between 0 and the total number of days between the "start" date and today.
cast(dateadd(DAY, rn, '20000101') AS DATE) AS [Date] --Adds the row number from the subquery "q" to the "start" date.
FROM (SELECT TOP (DATEDIFF(day, '20000101', GETDATE()) + 1) --TOP number of days between the "start" date and tomorrow.
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 rn --Numbers between 0 and the total number of days between the "start" date and today.
FROM sys.columns s1, sys.columns s2, sys.columns s3 --Cartesian product of sys.columns table three times. This is to generate a lot
--of data because we need a sequence between 0 and the total number of days between
--the "start" date and tomorrow.
) q
) qq;
What have I changed? :
1. I do place comma into start of line - it helps when I need to comment out some lines when debugging
2. ISO date format does not depend on your or my PC settings - it is ISO and its always YYYYMMDD
3. The [Date] column is of DATE datatype and it's only formatted by SSMS for displaying purposes based on your locale.
May 10, 2012 at 4:32 am
Eugene Elutin (5/10/2012)
Sorry Cadavre, I need to correct the comments for my code.What have I changed? :
1. I do place comma into start of line - it helps when I need to comment out some lines when debugging
2. ISO date format does not depend on your or my PC settings - it is ISO and its always YYYYMMDD
3. The [Date] column is of DATE datatype and it's only formatted by SSMS for displaying purposes based on your locale.
Ummm, OK. . . rebuttal? 😛
1. Not a correction, personal preference.
2. Indeed, but then you and I are not "new to SQL Server", as the OP is. So stating what ISO format means is necessary. may be necessary.
3. Same as point 2, the OP is new to SQL Server so may not know what will be displayed.
May 11, 2012 at 9:35 am
Here's my take. This one uses the Common Table Expression and doesn't depend on the sys.columns table.
WITH e1 (N) AS -- 10 Records
(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
, e4 ([N]) AS -- 10^4, or 10,000, Records
(SELECT a.[N]
FROM e1 AS a
CROSS JOIN e1 AS b
CROSS JOIN e1 AS c
CROSS JOIN e1 AS d
)
,cte_tally ([N]) AS -- numbers, starting from 0 and ending at 10,000
(SELECT 0 AS [N]
UNION ALL
SELECT ROW_NUMBER () OVER(PARTITION BY NULL ORDER BY [N]) AS [N]
FROM e4
)
SELECT -- Adding the number of days to the start date and ending with today's date
CONVERT(VARCHAR, DATEADD(DAY, N, CONVERT(DATE, '1/1/2000')), 112) AS [DateSk]
,DATEADD(DAY, N, CONVERT(DATE, '1/1/2000')) AS [Date]
,DATEPART(DAY, DATEADD(DAY, N, CONVERT(DATE, '1/1/2000'))) AS [Day]
FROM cte_tally
WHERE DATEADD(DAY, N, CONVERT(DATE, '1/1/2000')) <= GETDATE()
I'm not quite clear if the column "Day" is the day of the month or the number of days since the beginning. If it is the number of days from the beginning, then just use N+1.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply