Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert dates into table in the giving range. Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 11:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 5:49 AM
Points: 195, Visits: 497
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.
Post #1522155
Posted Thursday, December 12, 2013 12:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:46 AM
Points: 1,059, Visits: 697
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
Post #1522161
Posted Thursday, December 12, 2013 7:22 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 4,214, Visits: 3,244
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;




Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1522307
Posted Thursday, December 12, 2013 4:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:56 PM
Points: 106, Visits: 367
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.
SELECT	TOP(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
FROM sys.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.
Post #1522514
Posted Thursday, December 12, 2013 5:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 3,634, Visits: 5,283
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1522523
Posted Thursday, December 12, 2013 5:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:56 PM
Points: 106, Visits: 367
Nice!
Post #1522525
Posted Wednesday, January 1, 2014 1:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
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.
SELECT	TOP(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
FROM sys.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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526952
Posted Thursday, January 2, 2014 3:59 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,044, Visits: 3,060
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.
SELECT	TOP(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
FROM sys.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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1527313
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse