SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Creating custom column for dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pandyav
Starting Member

USA
2 Posts

Posted - 10/31/2013 :  17:07:18  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
273 Posts

Posted - 10/31/2013 :  17:45:29  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 11/01/2013 :  07:48:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

USA
2 Posts

Posted - 11/01/2013 :  12:43:33  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
273 Posts

Posted - 11/01/2013 :  12:54:42  Show Profile  Reply with Quote

select 
    ...,
    '1/' + CAST(period AS varchar(2)) + '/' + CAST(year AS varchar(4)) AS [<your_column_name_goes_here>],
    ...

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000