Converting a year end month to a date in the current year.

 Say we have a company's year end stored as a month field in a column in a database. The aim is to convert this integer year end month to a date in the current year.

For example:
The year end of company ABC is February. This is represented as a 2 in the database. The goal is to convert this to a date in the current year. So if it is 2011 then the aim is to generate the date, 2011-02-28. The next year the date should be 2011-02-28 etc.

The query:

select dateadd(day,-1, dateadd(month,1, convert(datetime,convert(char(4),year(getdate()))+'-'+convert(varchar,c.YEM)+'-'+'1'))) from (select 2 as YEM) c

Enjoy.

No comments:

Post a Comment