Query to Select records with max Date

[alexkeel79]

I have a table that stores the date in 3 seperate fields. One for Month, one for Day and one for Year. I am trying to write a query now that selects the MAX date. My initial plan was to concatenate the three fields together like so: YEAR + MONTH + DAY. Since I store the values as CHARS the result would be YYYYMMDD. However I run into a problem when the month or day is only one digit long. Does anybody know how I can import the Month and Day as two digits?

Ex. January 1, 2005:
Current - Y = 2005; M = 1; D = 1;
Desired - Y = 2005; M = 01; D = 01;

Or does anybody have a better way of getting the max date with three fields.

 

[dbomrrsm]

select right ('00'+Datefield,2)

[blue]DBomrrsm[/blue]

 

[JamesLean]

Or does anybody have a better way of getting the max date with three fields.

Yes - store the dates in a datetime field! ;-)

--James

 

[alexkeel79]

I decided to combine the three fields into one datetime field. Thanks for the help.