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
 General SQL Server Forums
 New to SQL Server Programming
 Year only from datetime

Author  Topic 

Derel
Starting Member

3 Posts

Posted - 2014-10-24 : 09:00:14
I have a column with datetime datatype and I need to create another column in the same table with only the year and another with year-month. How do I write this query

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-24 : 09:38:13
year(mydatetime)
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-24 : 09:41:02
and...year(mydatetime)*100+ month(mydatetime)
Go to Top of Page

Derel
Starting Member

3 Posts

Posted - 2014-10-24 : 09:52:05
Thanks gbritton, still a bit fuzzy, I thought would use the script below;
Alter Table table_name
ADD new_column name datatype,

to add the new column to the table. then populate this with a script that looksup the column with the timedate datatype and return only the year in the new column. my question is what scripts looks up the old column and populates my new column with only the year.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-24 : 10:28:12
after adding the new columns (I'm assuming they are ints):

update mytable
set year_only = year(mydatetime),
year_month = year(mydatetime)*100+ month(mydatetime)
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-24 : 10:28:55
for that matter why not just add two computed columns, or use a view to access the data and do the date math there?
Go to Top of Page

Derel
Starting Member

3 Posts

Posted - 2014-10-24 : 11:17:23
Thanks gbritton,
that was really helpful, regarding the computed columns, how's that done.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-24 : 12:06:50
Good discussion here: http://msdn.microsoft.com/en-us/library/ms188300.aspx

in your case:


create table mytable (
mydatetime datetime,
myyear as year(mydatetime),
myyear_month as year(mydatetime)*100+ month(mydatetime)
)
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-10-27 : 09:27:07
Yes intead of permanent columns, use it inside a view

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -