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 2008 Forums
 Transact-SQL (2008)
 Creating custom column for dates

Author  Topic 

pandyav
Starting Member

2 Posts

Posted - 2013-10-31 : 17:07:18
Hello,
I have two columns in my database:
Period - which incldues month numbers in a year, e.g. value of 1 represents January, 2 equal February and so on
Year - values include 2011, 2012 and so on
I want to create a custom column which will show result as 1/1/2011 if Period equals 1 and Year equals 2011 for a specific row.

Can someone please help me with how I can achieve this?
Very much appreciate your help in advance,

Vishal

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-31 : 17:45:29
You didn't state whether the result was month/day or day/month. I'll assume it's day/month; change the code below if it's the other way around.


ALTER TABLE dbo.tablename
ADD custom_column AS
'1/' + CAST(period AS varchar(2)) + '/' + CAST(year AS varchar(4))


Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-11-01 : 07:48:03
select select dateadd(month,month_col-1,dateadd(year,year_col-1900,0)) from table

Madhivanan

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

pandyav
Starting Member

2 Posts

Posted - 2013-11-01 : 12:43:33
quote:
Originally posted by ScottPletcher

You didn't state whether the result was month/day or day/month. I'll assume it's day/month; change the code below if it's the other way around.


ALTER TABLE dbo.tablename
ADD custom_column AS
'1/' + CAST(period AS varchar(2)) + '/' + CAST(year AS varchar(4))


Hello,
Thank you for the quick reply. I am looking for dd/mm/yyyy format. For some reason I am not able to execute the query with the statement you provided. Can you please help to understand where can I place the statement you provided? Instead of Alter a table, I just want to add a column in the results window that displays date in dd/mm/yyyy format.

The table that I am pulling the data from is scpsdbs_lob, month values are listed under 'period' column and there is a column called 'year' which has 4 digit year value. I am including the query I am using right now.


select order_grp, year, period,
net_sales = Sum(Case When category = 'ITEMS' and (QUANTITY < 0 AND UNIT_PRICE < 0 AND INV_TYPE <> 'ACR' )
Then (-1*([quantity]*[unit_price]))
Else case when category = 'ITEMS' then ([quantity]*[unit_price]) end
End),

order_grp_defined = case order_grp when 'RAM' then 'AMs'
when 'APPSEL' then 'Appliance_Select'
when 'ECOMM' then 'E-Commerce'
else 'Augusta' end,

Month = case period
when '1' then 'February'
when '2' then 'March'
when '3' then 'April'
when '4' then 'May'
when '5' then 'June'
when '6' then 'July'
when '7' then 'August'
when '8' then 'September'
when '9' then 'October'
when '10' then 'November'
when '11' then 'December'
else 'January' end,
count(distinct order_no) as order_count,
from scpsdbs_lob
where year in (2011, 2012, 2013)
group by order_grp, year, period

Very much appreciate your help in advance.










Vishal
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-11-01 : 12:54:42
[code]
select
...,
'1/' + CAST(period AS varchar(2)) + '/' + CAST(year AS varchar(4)) AS [<your_column_name_goes_here>],
...
[/code]
Go to Top of Page
   

- Advertisement -