Post reply

Convert and MAX Function

  • April 21, 2006 at 12:45 am

    #167973

    I have a dataset in RS that basically just popultes a report parameter field automatically for me. The small peice of SQL that I run is

    SELECT MAX (src_date) AS LatestMonth

    FROM dbo.tbl_src_date

    The problem I have is a visual one in that it all works but the output that is placed in the relevant report parameter box is mm/dd/yyyy hh:mm:ss.

    All I want to see in this box is dd/mm/yyyy and no time. On top of this I also wish it to select the hoghest date value it finds in the query result hence the MAX statement. How can I convert the datetime to the format I want and run a MAX statement at the same time.

    Or should I use the format command as it is only a visual thing? If yes, what is the SQL for that and MAX?

    Various SQL attempts (suggestions from other forums) have resulted in odd results, for example

    SELECT MAX(CONVERT(char(11), src_date)) AS LatestMonth

    FROM tbl_src_date

    Resulted in the date being shown as Sep 1 2005 and yet the highest date value in the table is 01/01/2006 (dd/mm/yyyy)

    Thanks in advance

    Toni

  • April 21, 2006 at 1:09 am

    #633416

    converting to soon.

    SELECT CONVERT(char(11), MAX(src_date)) AS LatestMonth

    FROM tbl_src_date

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • April 21, 2006 at 1:53 am

    #633423

    Thanks for the code snippet, using that and changing the parameter for that field in the report from DateTime to String allowed me to successfully run the report. I wonder if I might expand this question to you a little, is it possible to reduce the result set from Jan 1 2006 to Jan 2006, and also the following;

    I have 5 other date boxes, what I would like to have by default for those is the previous 5 months backwards from the src_date we have just changed the format of.

    For example, dt_src_date is 01/01/2006 (displayed as Jan 2006), I would like to have a query that looks at the MAX figure in the src_date table and counts back one month e.g. 01/12/2005 (displayed as Dec 2005), the next box would count back two months etc etc.

    Is that simple enough to do?

    Regards

    Toni

     

  • April 21, 2006 at 2:01 am

    #633424

    I'm not that much into reporting server, but you can also have this solved in your select query

    SELECT CONVERT(char(11), MAX(src_date)) AS LatestMonth

    , CONVERT(char(11), dateadd(mm,-1, MAX(src_date))) AS [LatestMonth-1]

    , CONVERT(char(11), dateadd(mm,-2, MAX(src_date))) AS [LatestMonth-2]

    , CONVERT(char(11), dateadd(mm,-3, MAX(src_date))) AS [LatestMonth-3]

    , CONVERT(char(11), dateadd(mm,-4, MAX(src_date))) AS [LatestMonth-4]

    , CONVERT(char(11), dateadd(mm,-5, MAX(src_date))) AS [LatestMonth-5]

    FROM tbl_src_date

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • April 23, 2006 at 4:12 am

    #633677

    Thanks, that solved my issue nicely.

    Regards

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply