December 11, 2013 at 11:45 pm
Hi,
I am having a table with SchoolDate column. In this column I need to enters all the dates in the range.
for example start date = 02/25/14 and EndDate = 04/26/14 then i need to insert all the dates i.e.
02/25/14
02/26/14
02/27/14
.
.
.
.
.
.
04/26/14 .
Could you please help me?
Thanks
Abhas.
December 12, 2013 at 12:08 am
We can use Identity for below ones only...
data types for an identity column are any data types of the integer data type category, except for the bit data type, or decimal data type.
http://technet.microsoft.com/en-us/library/ms189838.aspx for more info..
Option 1:If you are inserting of records only...Add CHECK Constraint
Option 2: For automatic population of Date in that column
Create a tabular function which can take startdate and Enddate and gives result set as u expected.
Update Ur Schooldate column with Resultset returned by function.
!!!!!!!
Sasidhar Pulivarthi
December 12, 2013 at 7:22 am
abhas (12/11/2013)
Hi,I am having a table with SchoolDate column. In this column I need to enters all the dates in the range.
for example start date = 02/25/14 and EndDate = 04/26/14 then i need to insert all the dates i.e.
02/25/14
02/26/14
02/27/14
.
.
.
.
.
.
04/26/14 .
Could you please help me?
Thanks
Abhas.
Here's a select statement to return a table of dates within a range. It uses a tally table. If you're not familiar with a tally table yet, read the article at http://www.sqlservercentral.com/articles/T-SQL/62867/ and take the time to get familiar with them. It'll change the way you look at data.
select DATEADD(DAY, t.N - 1, CONVERT(datetime, '02/25/2014'))
from Tally1K t
where t.N < DATEDIFF(DAY, '02/24/2014', '04/26/2014') + 1
order by 1;
December 12, 2013 at 4:02 pm
If you don't want to create a Tally table, you can use sys.columns instead and create a tally number in memory. According to my research, this is actually faster than reading a Tally table from the disc.
SELECTTOP(DATEDIFF(DAY, '02/24/2014', '04/26/2014') + 1)
DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, CONVERT(DATETIME, '02/24/2014')) AS ItemDate
FROMsys.columns a CROSS JOIN sys.columns b
The "CROSS APPLY" in the "FROM" clause may not be necessary if you are adding only a few values each time.
December 12, 2013 at 5:01 pm
Or you can skip creating the table all together and use a calendar generating FUNCTION instead:
CREATE FUNCTION [dbo].[GenerateCalendar]
(
@FromDate DATETIME,
@NoDays INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== High speed code provided courtesy of Jeff Moden (idea by Dwain Camps)
--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
SELECT [SeqNo] = t.N,
[Date] = dt.DT,
[Year] = dp.YY,
[YrNN] = dp.YY % 100,
[YYYYMM] = dp.YY * 100 + dp.MM,
[BuddhaYr] = dp.YY + 543,
[Month] = dp.MM,
[Day] = dp.DD,
[WkDNo] = DATEPART(dw,dt.DT),
[WkDName] = CONVERT(NCHAR(9),dp.DW),
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
[JulDay] = dp.DY,
[JulWk] = dp.DY/7+1,
[WkNo] = dp.DD/7+1,
[Qtr] = DATEPART(qq,dt.Dt),
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY, Acourtesy of ChrisM)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)
) dp
GO
SELECT [Date]
FROM dbo.GenerateCalendar('02/25/2014', 1+DATEDIFF(day, '04/26/2014', '02/25/2014'))
ORDER BY [Date];
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 12, 2013 at 5:05 pm
Nice!
January 1, 2014 at 1:15 pm
fahey.jonathan (12/12/2013)
If you don't want to create a Tally table, you can use sys.columns instead and create a tally number in memory. According to my research, this is actually faster than reading a Tally table from the disc.
SELECTTOP(DATEDIFF(DAY, '02/24/2014', '04/26/2014') + 1)
DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, CONVERT(DATETIME, '02/24/2014')) AS ItemDate
FROMsys.columns a CROSS JOIN sys.columns b
The "CROSS APPLY" in the "FROM" clause may not be necessary if you are adding only a few values each time.
I'd actually like to see the proof of that, Jonathan. Normally, a small Tally Table of, say 11K rows or less will stay cached and won't actually be read from the disc. I'd also like to see the "form" of the Tally Table you tested with and the test code. People frequently forget such things as the addition of a Clustered Index on the Tally Table and/or to apply a properly pre-calculated limit (Like the TOP clause you used above) for values of "N" in the Tally Table.
I'll also recommend that you use master.sys.all_columns instead of just sys.columns because it will always contain at least 4K rows even on a brand new system.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2014 at 3:59 pm
Jeff Moden (1/1/2014)
fahey.jonathan (12/12/2013)
If you don't want to create a Tally table, you can use sys.columns instead and create a tally number in memory. According to my research, this is actually faster than reading a Tally table from the disc.
SELECTTOP(DATEDIFF(DAY, '02/24/2014', '04/26/2014') + 1)
DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, CONVERT(DATETIME, '02/24/2014')) AS ItemDate
FROMsys.columns a CROSS JOIN sys.columns b
The "CROSS APPLY" in the "FROM" clause may not be necessary if you are adding only a few values each time.
I'd actually like to see the proof of that, Jonathan. Normally, a small Tally Table of, say 11K rows or less will stay cached and won't actually be read from the disc. I'd also like to see the "form" of the Tally Table you tested with and the test code. People frequently forget such things as the addition of a Clustered Index on the Tally Table and/or to apply a properly pre-calculated limit (Like the TOP clause you used above) for values of "N" in the Tally Table.
I'll also recommend that you use master.sys.all_columns instead of just sys.columns because it will always contain at least 4K rows even on a brand new system.
I'd strongly recommend you don't use any sys. table at all for this, especially one in the master database. No need for such a complication for such a simple task.
IIRC, Itzik Ben-Gan was the first to point out the performance advantages of using CROSS JOIN vs. reading from a physical tally table (and that a recursive CTE to gen the tally table was far worse than either of those options).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply