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
 Development Tools
 Reporting Services Development
 Managing Dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mshsilver
Posting Yak Master

110 Posts

Posted - 11/07/2013 :  07:38:13  Show Profile  Reply with Quote
Hi,
I was wondering if there was a simple way to transform dates held in a field in a SQL table from dd/mm/yyyy format to translate to months and years when producing graphical reports in SSRS.

Many databases are designed to hold the month and year in separate fields. Unfortunately the database I am using does not do this, there is just one createtime field formatted in dd/mm/yyyy and therefore i can't break down sales into months and years which is what most users want to see.

I know I could add a month and year field and come up with some logic and queries to look at the createtime field and break down dd/mm/yyyy but that is not great as it would have to be run a lot as new data is going into this table regularly. The front-end of the software is not going to change either so I can’t get this information in the month and year fields from point of entry.

I was hoping someone might have come across this issue and have a solution or an idea of how this could be handled efficiently.

Thanks for looking.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/07/2013 :  08:03:03  Show Profile  Reply with Quote
you've DatePart,Month,Year etc functions available in SSRS which you can use and get year and month information from date field in your reports. You have to bring them as datetime datatype itself for applying these functions.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mshsilver
Posting Yak Master

110 Posts

Posted - 11/07/2013 :  08:59:56  Show Profile  Reply with Quote
Thank you for this, just to clarify as this is all new to me. Do you mean this will be a function built into your SQL query that retrieves the dataset you want to display in the report? I've Googled a bit and I can't find an example in ssrs but i can find queries like the following:

SELECT DATEPART(yyyy,createtime) AS OrderYear,
DATEPART(mm,createtime) AS OrderMonth,
DATEPART(dd,createtime) AS OrderDay
FROM sales
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/07/2013 :  09:06:54  Show Profile  Reply with Quote
quote:
Originally posted by mshsilver

Thank you for this, just to clarify as this is all new to me. Do you mean this will be a function built into your SQL query that retrieves the dataset you want to display in the report? I've Googled a bit and I can't find an example in ssrs but i can find queries like the following:

SELECT DATEPART(yyyy,createtime) AS OrderYear,
DATEPART(mm,createtime) AS OrderMonth,
DATEPART(dd,createtime) AS OrderDay
FROM sales



nope
you've similar functions in SSRS also
DatePart(),Year(), MOnth() etc

http://www.venkateswarlu.co.in/MSBI/ssrs/ssrs_common_functions_date_time.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.06 seconds. Powered By: Snitz Forums 2000