Setting Quarter valuie - 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 2000 Forums

Transact-SQL (2000)

Setting Quarter valuie

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-04-24 : 06:40:18

Hi i'd like to not have to hard code my quarter name into the code for the given year so i'd like to uses the year colum in my table and just hard code in the _Q1, _Q2 etc..
Is it possible to do this in my set statment i've given a rough idea of what am after in the below statment

UPDATE tbl_temp_yrs
SET [Quarter] = cast(datepart(yy,[year])+''+'_Q1'
where ([month] in ('January','February','March') and [year] = datepart(yy,[year]))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 06:44:18

you can use datepart(quarter, datecol) to find the quarter.


UPDATE tbl_temp_yrs
SET [Quarter] = convert(varchar(4), datepart(year, datecol)) + '_Q' + convert(varchar(1), datepart(quarter, datecol))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 06:44:24

UPDATE tbl_temp_yrs
SET [Quarter] = cast([year] as varchar) + case when [month] in ('January', 'February', 'March') then '_Q1'
when [month] in ('April', 'May', 'June') then '_Q2'
when [month] in ('July', 'August', 'September') then '_Q3'
when [month] in ('October', 'November', 'December') then '_Q4'
end

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-24 : 06:46:12

quote:

Originally posted by khtan

you can use datepart(quarter, datecol) to find the quarter.

UPDATE tbl_temp_yrs
SET [Quarter] = convert(varchar(4), datepart(year, datecol)) + '_Q' + convert(varchar(1), datepart(quarter, datecol))

UPDATE tbl_temp_yrs
SET [Quarter] = datename(year, datecol) + '_Q' + datename(quarter, datecol)

Somewhat smaller and easier to read.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 06:50:23

quote:
Originally posted by Peso

quote:
Originally posted by khtan

you can use datepart(quarter, datecol) to find the quarter.
UPDATE tbl_temp_yrs
SET [Quarter] = convert(varchar(4), datepart(year, datecol)) + '_Q' + convert(varchar(1), datepart(quarter, datecol))

UPDATE tbl_temp_yrs
SET [Quarter] = datename(year, datecol) + '_Q' + datename(quarter, datecol)
Somewhat smaller and easier to read.

Peter Larsson
Helsingborg, Sweden

good idea

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-04-24 : 06:55:02

Thanks guys, thats exactly the result i was looking for eg 2007_Q1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources