Perform Insert taking care Local-Solar days

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 2008 Forums

Transact-SQL (2008)

Perform Insert taking care Local-Solar days

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-11-08 : 05:44:09

Hello all,
I have a simple query that make an Insert of one record in one table, in this way:

Declare @MyDate datetime
set @MyDate = '2010-06-06 22:00:00.000'

INSERT INTO T_TaskQueue (NameTask, ReferenceDate, IdObject)
VALUES ('MyTask1', @MyDate, 0)

Now I should make this script able to accept a StartDate and an EndDate
and make these Insert for every day between this dates.
But I have to insert hour=22 for days "legal" (from last sunday in March to last sunday in October)
and hour=23 for the other days.

How can I make this?

Thanks in advance.

Luigi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 06:13:07

Probably the simplest and most straightforward way is to create a calendar table and join with that table. Create the calendar table with two columns, one for the date and the other for the hours - like shown below

CREATE TABLE #Calendar(dt DATETIME, hourstoInsert INT);

Now populate this table for the date ranges that are of interest to you - for example, to do this for all of 2012, you would do the following:

;WITH N(n) AS
(
    SELECT 1 UNION ALL SELECT n + 1
                       FROM   N
                       WHERE  n < 366
)
INSERT INTO #Calendar
SELECT DATEADD(dd, n -1, '20120101'), 23
FROM   N
       OPTION(MAXRECURSION 0);

I inserted 23 for all hours, you can run an update to change that to 22 for the dates of your interest.

Once you have this table, insert into your destination table by selecting from this table

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-11-08 : 08:13:47

Yes I have a table T_Date, with fields DateTime and DateTimeUTC.
So how can I change my query?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 08:39:58

Would be something like this, assuming the date column in T_Date is "DateColumn"

Declare @MyStartDate DATETIME, @MyEndDate DATETIME;
set @MyStartDate = '20100626';
SET @MyEndDate = '2010731';

INSERT INTO T_TaskQueue (NameTask, ReferenceDate, IdObject) 
SELECT
	'MyTask1',
	Datecolumn,
	0
FROM
	T_Date
WHERE
	DateColumn BETWEEN  @MyStartDate AND @MyEndDate;

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-11-08 : 08:48:22

Perfect, thank you very much Sunita.

Luigi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources