Calendar table - SQL Server Forums

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

Transact-SQL (2005)

Calendar table

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2010-01-21 : 17:33:38

Hi,
I have a table with unique machine numbers in a table.
Those are 01, 02, 05, TS, MS, KS

I have to come up with a sql table which will display as follows:

machine plan_date plan_hrs

01 01/01/2010 16
01 01/02/2010 16

.....

This will repeat for 365 days in 2010 and then

rest of the machines will repeat each for 365 days.

I am having difficulty in formulating the calendar i.e. the plan_date column.

I would appreciate any help here.
Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-21 : 17:51:55

like this:

declare @test table (machine varchar(255), plan_date datetime, plan_hrs int)
declare @machines table (machine varchar(255))
insert @machines
select '01' union all
select '02'

declare @days int
set @days=0
while (@days < 365)
begin
 insert @test select machine,dateadd(dd,@days,'20100101'),16 from @machines
 set @days=@days+1
end
select * from @test

No, you're never too old to Yak'n'Roll if you're too young to die.

gamaz
Posting Yak Master

104 Posts

Posted - 2010-01-22 : 12:44:46

Thanks wefred for your generous help. It works great.
However the date is in the following format 2010-01-01 00:00:00.000
However I need the date to be like 01-01-2010. How with the code change to accommodate the date in this format.
Thanks.

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 13:07:03

SQL DATETIME datatype is stored in an "internal format"

You can format however you like, but it is better to do this in your application, and not in SQL itself (otherwise you lose the fact that the object is date/time, and it becomes String, and all sorts of other problems arise when trying to manipulate the value.

If you need to convert it in SQL Server see the CONVERT() function which offers a parameter for formatting style of the date when converting to varchar/char data types.

gamaz
Posting Yak Master

104 Posts

Posted - 2010-01-22 : 14:33:14

Thanks Kristen for the nice explanation. I will probably leave it the way it is and look into my application for manipulating the date format.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources