September 2, 2008 at 3:10 pm
Hello All, I was following a little bit of the code on this topic for another member. I need some help on Inserting Date (e.g. 01-01-2008) and it's corresponding Day-Of-Week (e.g. Tuesday), this is what I have in my MS SQL (t-sql) table:
my short version of my table has:
id (int), day nchar (10), date (smalldate)
1 Tuesday 01-01-2008
2 Wednesday 01-02-2008
... and so forth ...
I need to insert dates into this simple table from 01-01-2008 -to- 12-31-2012 (5 years). Can some one help me with the code for it. currently my table is empty so it won't conflict with the insert into it.
Thanks.
September 2, 2008 at 3:20 pm
This answers your question but I don't quite understand why you are doing this.... (No table name provided so created table "DayOfWeekDates")
--SET START OF WEEK TO BE MONDAY (SUNDAY IS BY DEFAULT)
SET DATEFIRST 1
--DECLARE ALL VARIABLES
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
--ASSIGN VALUES TO TEMP VARIABLES
SET @StartDate = '1/1/2008'
SET @EndDate = '12/31/2012'
--START LOOP
--LOOP UNTIL @StartDate IS GREATER THAN @EndDate
WHILE @StartDate <= @EndDate
BEGIN
--INSERT RECORDS TO TABLE
INSERT INTO
DayOfWeekDates
(
[Day]
,[Date]
)
SELECT
CASE DATEPART(DW, @StartDate)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
WHEN 7 THEN 'Sunday'
END AS DayOfWeek
,@StartDate AS ActualDate
--INCREASE @StartDate BY ONE DAY
SET @StartDate = DATEADD(DD, 1, @StartDate)
END
GO
September 2, 2008 at 3:52 pm
You can use the function on this link for what you want to do.
Date Table Function F_TABLE_DATE
September 2, 2008 at 4:05 pm
Awesome Grasshopper! it worked!
My SQL table will be taking "daily counts" daily for the next 5 years. So I wanted to create an empty record for each day of the year showing the Day and Date, the daily counts will consist of How many: "meals", 'breakfast", 'dinner', "visitors", "pieces_of_clothes", etc. etc. I want to track items given to the homeless on a daily basis. My next challenge is to create a web driven site (using asp.net, visual studio '08) so the volunteer computer users can enter those numbers at the end of the date.
Thank you for your help.
Vic.
September 2, 2008 at 4:10 pm
With a little DB design you should not have to do this table....
FYI... my ID is parackson my rank on this site is Grasshopper
September 2, 2008 at 6:29 pm
While you CAN make a database that uses the actual date, the question sometimes becomes SHOULD I.
We have a table with over 15.5 billion rows in it (increases daily by about 70 million). We have converted the date to an integer so that we could save drive space and to improve query performance. It has worked great. Doing this has saved us over 120 gigs of drive space!
Gary Johnson
Sr Database Engineer
September 2, 2008 at 8:05 pm
Thank you guys, I really appreciate your super quick response and also for sharing your experiences. Your advice has helped me!
Keep it up!!!
Victorio.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply