'Convert' and 'MAX' Functions (1 Viewer)

Ice Rhino · Apr 20, 2006

I have a dataset in Reporting Services that basically just populates 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?

Thanks in advance

SQL_Hell · Apr 20, 2006

hi there

Try this,

SELECT MAX (CONVERT(SMALLDATETIME,src_date,103)) AS LatestMonth
FROM dbo.tbl_src_date

Ice Rhino · Apr 20, 2006

Fantastic, Thanks SQL Hell.

Tell me, do you just live in this Forum as 9 / 10 you answer any query in minutes

Thanks once again

Ice Rhino · Apr 20, 2006

Hi SQL,

It is still coming up with the hh:mm:ss in the query result. What have I done wrong here?

Regards

SQL_Hell · Apr 20, 2006

Yeah this is tricky to get working for some reason, I will have a go with some data and get back to you mate.

I will answer your other post in due course

Ice Rhino · Apr 20, 2006

Thanks SQL H, I shall look forward to your next pearl of wisdom and insight into the murky confusing world of SQL Server.

Kindest Regards

SQL_Hell · Apr 20, 2006

ok try this

SELECT MAX (left(src_date,11)) AS LatestMonth
FROM dbo.tbl_src_date

there is some way you can do it with using the CONVERT function, but I can't remember it.

I don't really spend my life on here, but I do use it when we haven't got any support issues at work. I suppose its just a coincidence that I have answered yours quickly

I am not a geek honest

Ice Rhino · Apr 20, 2006

If this does not work then what about the format command as the purpose of this is purely visual?

If you say, 'Yeah, that would work!'
You know what the next question is though don't you

Hey nothing wrong with being a Geek if you are geeky enough to answer my dull questions. Keep up the support like this and I might pay for a subscription to 'Geek Central', there is bound to be one somewhere and I shall make it my mission to find you within it.

Thanks SQL Hell, off to check your solution.

Kindest Regards

Ice Rhino · Apr 20, 2006

Ok this result has confused me

I placed the code you suggested in the SQL window of the Dataset and was presented with the result of

Sep 1 2005

The highest date I have in my DB is 01/01/2006 (dd/mm/yyyy) so how it came up with Sep 1st '05 is a mystery to me.

Regards

Ice Rhino · Apr 20, 2006

Not sure if this is relevant or not, but I have just been playing with that syntax you provided in SQL Server Query (not Reporting services)

SELECT MAX(CONVERT(SMALLDATETIME, src_date, 103)) AS LatestMonth
FROM tbl_src_date

and no matter what style code I put in it is always the same result, even if I take out the MAX options

The style it defaults to is

2006-01-01 00:00:00

and yet when I open the table and look at the data it is already dd/mm/yyyy.

Just thought this might help

Regards

SQL_Hell · Apr 20, 2006

Ice Rhino said:
Ok this result has confused me

I placed the code you suggested in the SQL window of the Dataset and was presented with the result of

Sep 1 2005

The highest date I have in my DB is 01/01/2006 (dd/mm/yyyy) so how it came up with Sep 1st '05 is a mystery to me.

Regards

Hi I cant think for the life of me why that is hapening, It's not happening at my end

try this...

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

or this

SELECT CONVERT(char(11), src_date)) AS LatestMonth
FROM tbl_src_date
where src_date = (select max(src_date) from tbl_src_date

Ice Rhino · Apr 21, 2006

Hi SQL Hell

Thanks for your further responses. First code attempted still returned

Sep 1 2005

Second peice of code displayed a unable to Parse Query error

I don't get this Sep 1 2005 bit. Even if you reverse the date I have there to mm/dd/yyyy it still isn't Septemer.

?? I am going to run some tests against the datasoucre to ensure there is not something screwy going on in there. There are only 23 or so entries so won't take long.

Regards

Ice Rhino · Apr 21, 2006

Got this answer from a different forum which seems to work as expected

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

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

Ice Rhino · Apr 21, 2006

Have manageed to answer this with

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

and then dateadd(mm,-2, blah blah

Works fine. Thanks for all your efforts SQL Hell

Regards

'Convert' and 'MAX' Functions (1 Viewer)

Ice Rhino

Registered User.

SQL_Hell

SQL Server DBA

Ice Rhino

Registered User.

Ice Rhino

Registered User.

SQL_Hell

SQL Server DBA

Ice Rhino

Registered User.

SQL_Hell

SQL Server DBA

Ice Rhino

Registered User.

Ice Rhino

Registered User.

Ice Rhino

Registered User.

SQL_Hell

SQL Server DBA

Ice Rhino

Registered User.

Ice Rhino

Registered User.

Ice Rhino

Registered User.

Similar threads

Users who are viewing this thread