February 7, 2013 at 2:28 am
Morning Everyone,
I have just started training on SQL and was wondering if you could help with a problem I have encountered.
I have populated a column with a date via the CREATE TABLE Function
USE Occupancy
CREATE TABLE Time
(Date date not null)
DECLARE @StartDate Date,
@EndDate Date
SET @StartDate = '01 Jan 2010'
SET @EndDate= '31 March 2015'
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO Time
VALUES (@StartDate)
SET @StartDate = Dateadd(dd,1,@StartDate)
END
But what I would like to do now is create 4 Extra columns called CalendarYear, CalendarMonth, FinancialYear and finally FinancialMonth, I know I should use the Datepart function but not matter how many time I have written it, it doesn't seem to work.
All help is much appreciated.
Thanks
Wayne
February 7, 2013 at 3:44 am
Can you post the code that you have tried.
As you are learning I would strongly recommend that stop using While loops/Cursors these are notoriously inefficient, in addition you will learn a lot of bad habits that will be difficult to break down the line.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 7, 2013 at 3:53 am
Hi Jason
I have been on this now for hours and getting so frustrated, you mentioned yesterday about loops on another post I had but my boss wants me to use it, all he wants me to do now is add the extra columns.
I used (SELECT Datepart(YEAR, '@StartDate') AS CalendarYear) but it didn't work and to be honest I don't know why.
Code so far:
USE Occupancy
CREATE TABLE Time
(Date date not null)
DECLARE @StartDate Date,
@EndDate Date
SET @StartDate = '01 Jan 2010'
SET @EndDate= '31 March 2015'
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO Time
VALUES (@StartDate)
SET @StartDate = Dateadd(dd,1,@StartDate)
(SELECT Datepart(YEAR, '@StartDate') AS CalendarYear)
END
Thanks for you help in advance.
Wayne
February 7, 2013 at 4:01 am
I wonder if hes teaching you the pain of using whiles. 😀
Anyway back to the question at hand.
Firstly you've placed the @StartDate in single quotes you shouldnt
The statement should read
SELECT DATEPART(Year,@StartDate) AS CALENDARYEAR
Next are you meant to be adding this columns to the Table Time that you have as you will need to alter the table to have a add a CalendarYear.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 7, 2013 at 4:08 am
You're right about pain, he said I will give you a easy task first, I have been trying to figure this oout now for a day and a half. Anway back to the task at hand:
The final table should look something like this:
Date CalendarYearCalendarMonthFinancialYearFinancialMonth
01 Jan 20112011 January 2010 January
02 Jan 20112011 January 2010 January
03 Jan 20112011 January 2010 January
04 Jan 20112011 January 2010 January
Ta
Wayne
February 7, 2013 at 5:42 am
good luck, I hope this is being used as a 'wrong' way of doing something rather than the right way.
I would pick up a couple of books on SQL programming, the ones I would recommend are SQL 2008 Fundamentals and Inside SQL Server Programming TSQL both by Itzik Ben-gan.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 7, 2013 at 8:21 am
SELECT Datepart(YEAR, '@StartDate') AS CalendarYear)
The issue there is you have passed a string as the datetime because you wrapped your variable with tick marks.
Try this instead.
SELECT Datepart(YEAR, @StartDate) AS CalendarYear)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 8:30 am
wafw1971 (2/7/2013)
Morning Everyone,I have just started training on SQL and was wondering if you could help with a problem I have encountered.
I have populated a column with a date via the CREATE TABLE Function
USE Occupancy
CREATE TABLE Time
(Date date not null)
DECLARE @StartDate Date,
@EndDate Date
SET @StartDate = '01 Jan 2010'
SET @EndDate= '31 March 2015'
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO Time
VALUES (@StartDate)
SET @StartDate = Dateadd(dd,1,@StartDate)
END
But what I would like to do now is create 4 Extra columns called CalendarYear, CalendarMonth, FinancialYear and finally FinancialMonth, I know I should use the Datepart function but not matter how many time I have written it, it doesn't seem to work.
All help is much appreciated.
Thanks
Wayne
A loop is horribly inefficient as a way to learn this stuff. I would recommend using computed columns instead of storing values. If you have the date portions stored in their own columns how are you going to handle updates? I can see that having dateparts could be useful in very large tables but do NOT store this as data.
Here is an example of using the MONTH as a computed column. Imagine your table and what you have to do if you update the date like in the update statement in the example. You would have to have a trigger on your table to update the other columns. Yuck!!!
if object_id('tempdb..#MyDate') is not null
drop table #MyDate
CREATE TABLE #MyDate
(
SomeID int identity,
SomeDate datetime,
SomeMonth as datepart(MONTH, SomeDate) persisted
)
insert #MyDate
select getdate()
select * from #MyDate
update #MyDate
set SomeDate = '2013-07-01'
select * from #MyDate
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 8:31 am
And also since you say you are learning. You need to avoid using reserved words for object names. Tables named Time and columns named Date will cause you untold pain as you try to query those tables.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 8:44 am
Just to demonstrate how incredibly inefficient cursors can be. Take a look at this insert. It uses the tally table which can be found here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
This code will insert the same 1,916 rows as your while loop. It will perform all of the inserts in about the blink of an eye, literally.
if object_id('tempdb..#MyTime') is not null
drop table #MyTime
create table #MyTime
(
MyDate date not null
)
insert #MyTime
select dateadd(DAY, N - 1, '2010-01-01')
from Tally
where N <= datediff(day, '2010-01-01', '2015-03-31') + 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 8:55 am
Sean, I've alread shown a simle proof for Tally vs Loop on this thread (http://www.sqlservercentral.com/Forums/Topic1416507-1292-1.aspx) on
Unfortunately hes been told to use the While loop by his boss. My hope is that his boss is teaching him the wrong way before he shows him the right way.
Edit : for link to previous post.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 7, 2013 at 9:00 am
Jason-299789 (2/7/2013)
Sean, I've alread shown a simle proof for Tally vs Loop on this thread (http://www.sqlservercentral.com/Forums/Topic1416507-1292-1.aspx) onUnfortunately hes been told to use the While loop by his boss. My hope is that his boss is teaching him the wrong way before he shows him the right way.
Edit : for link to previous post.
Well that being in the other thread I had no idea. 😉
Of course in the case of this situation the approach is not the best. The computed column is a far better choice in this case.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 9:14 am
I would like to say thank you for all you help, I have now created the table (the wrong way). And now have another task to complete (See other thread about sort/order by).
February 7, 2013 at 9:27 am
wafw1971 (2/7/2013)
I would like to say thank you for all you help, I have now created the table (the wrong way). And now have another task to complete (See other thread about sort/order by).
You should ask your boss if this data will ever get updated and how he plans on having you keep these other columns in synch.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply