Year only from datetime - 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

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)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-24 : 09:41:02

and...year(mydatetime)*100+ month(mydatetime)

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.

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)

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?

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.

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)
)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources