convert a date and then sort it

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I have date field that stores the dates in m/dd/yyyy format, I need to convert the dates to yyyy/mm format without changing the format of the field in the table and then sort by the oldest to the newest. How can this be accomplished.

Thanks
 
If it is a true date field, then you can sort it..and NOT worry about the
display.

If the field is a text field..then you should convert it to a date field.
Date fields are great since you can display in any format. You can even
display a date field as

Sunday, June 13, 2004

And it will sort in correct order.

And, if your field is a date field now..just sort..and it will sort
correctly.

You can open up the table in design mode..and change the field to a date (I
would test/try this on a copy of the database first).
 
Okay, my field is a date datatype, my problem is that i need to extract the year and month from the data and sort by that.
i've tried several different ways to accomplish this, such as using DatePart, and Month functions. However when I sort, October 2003,(200310) is at the top of the list and September 2003, (20039) is at the bottom of the list after December 2003,(200312), and I cannot figure out how to correct this problem.

Thanks
 
Not sure what you used..but why not just use Year, and Month functions?

You are confusing some string that you extract for display.

You can continue to display/show your existing date part with your
extraction..but don't sort on that displayed value.

Just sort on the year/month columns that you add. In the query builder go:

MyMonth:month([yourdatefield])

MyYear:year([yourdatefield])

Now...just set the sorting for those above fields in the query builder....

And, point in fact..you might just include the actaul datefield..and sort by
that?
 
or make sure you use a format of "yyyymm" which will return a four-character
year and a two-character month. That will sort in year and month order, while a
format of "yyyym" will not.
 
Back
Top