Date Convert - 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

General SQL Server Forums

New to SQL Server Programming

Date Convert

Author

Topic

simmark333
Starting Member

5 Posts

Posted - 2007-04-26 : 18:37:12

Good Afternoon,
I have posted here before and have gotten excellent feed back from you SQL Gurus! I would have to say that date converts are my kryptonite! My problem is that I have multiple accounts that are tied to multiple dates on a server. I need to pull the MAX(date) of the account numbers; however, the date is formatted MMYY. For example;

Table
Account Date
123456 1206
123456 507
987654 107
987654 1106
987654 806
123456 805
123456 306

If I select the MAX date for Account 123456 it will give me '1206' when clearly '107’ is the most recent date. Can anyone provide me a script that will format the date so that SQL will understand what I am trying to accomplish? Also, the day is not important; it can be defaulted to 01. - Thank you very much

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 18:40:30

SELECT Account, MAX(RIGHT(Date, 2) + LEFT(RIGHT('0000' + Date, 4), 2))
FROM Table
GROUP BY Account

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 18:51:11

[code]
SELECT Account, MAX(RIGHT(Date, 2) + LEFT(RIGHT('00' + DATE, 4),2)) -- Convert to YYMM before MAX it
FROM Table
GROUP BY Account
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 19:00:05

Peter Larsson
Helsingborg, Sweden

joblenis
Starting Member

29 Posts

Posted - 2007-05-02 : 16:26:56

LOL.. @ Peso

What else was funny was the poster said clearly '107' was the lastest date for account '123456' when that date does not exist for that account, the latest would be '507' ... clearly

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-04 : 06:20:48

<<
however, the date is formatted MMYY.
>>

Store valid dates in table and let front end do the formation

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources