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.
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 onYear - 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.tablenameADD custom_column AS '1/' + CAST(period AS varchar(2)) + '/' + CAST(year AS varchar(4)) |
|
|
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 tableMadhivananFailing to plan is Planning to fail |
|
|
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.tablenameADD 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]) endEnd),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, periodVery much appreciate your help in advance.
Vishal |
|
|
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] |
|
|
|
|
|
|
|