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 |
|
mshsilver
Posting Yak Master
112 Posts |
 Posted - 2013-11-07 : 07:38:13
|
| 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
52326 Posts |
Posted - 2013-11-07 : 08:03:03
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
mshsilver
Posting Yak Master
112 Posts |
Posted - 2013-11-07 : 08:59:56
|
| 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 OrderDayFROM sales |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 09:06:54
|
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 OrderDayFROM sales
nopeyou've similar functions in SSRS alsoDatePart(),Year(), MOnth() etchttp://www.venkateswarlu.co.in/MSBI/ssrs/ssrs_common_functions_date_time.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Managing Dates