MIN/MAX issue....!! - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

MIN/MAX issue....!!

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-02-21 : 08:26:29

Hello

I have a very annoying isue that is bugging the hell out of me.....!!

I have a course date field in a select statement. Some people can be on multiple courses but I need to know the latest one....sounds simple right...!!

If I don't apply a MAX filter I get multiple course dates for people as they can be doing 3 or 4 courses, which is expected. However when I apply the MAX filter to it I get the one person, but some individuals have the MAX date correctly displaying but some have the MIN date displaying - which I find really strange...!!

Has anybody an idea why it might do this?

Could it be anything to do with the date format on the datefield which seems to be a normal datetime field?

or do i have to use the group by claus or something...??

My Code is something like:

SELECT
MAX(CONVERT(varchar, coursedate, 103)) AS [Exp End Date],
FROM
TABLE

Any help would be much appreciated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 08:32:00

Conversion to varchar is the cause of the problem. Why you are converting to varchar?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-21 : 08:33:07

Do you want to find max date for each person?

SELECT person,
MAX(coursedate) AS [Exp End Date],
FROM
TABLE
GROUP BY person

Madhivanan

Failing to plan is Planning to fail

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-02-21 : 09:39:43

I'm creating a view of a date time field within a table.

If i don't convert the field I get an error saying:

Conversion failed when converting datetime from character string.

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-02-21 : 09:46:46

The actual datefield format is:

2005-07-31 00:00:00.000
2005-07-31 00:00:00.000
2005-07-31 00:00:00.000
2005-07-31 00:00:00.000
2003-10-31 00:00:00.000
2003-10-31 00:00:00.000

I need it to be a standard UK format dd/mm/yy
which is why i'm converting it.
Is there another way of converting other than using convert?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-21 : 12:04:57

Why dont you leave the date as it is & try to format in your presentation layer(front end)?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-21 : 13:09:02

SELECT person,
CONVERT(VARCHAR(10), MAX(coursedate), 103) AS [Exp End Date],
FROM TABLE1
GROUP BY person

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-21 : 13:53:02

Don't convert your nice DateTime values to VARCHAR -- always return raw data in the correct data type from your database and let your client or presentation layer do the formatting.

- Jeff
http://weblogs.sqlteam.com/JeffS

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-02-22 : 05:58:27

Thanks guys problem now resolved.

Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources