Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

rs.sort by year on date field?

I need to sort a recordset by the year of a date field.

right now I have: rs.sort = "BIRTHDATE DESC"

I have tried rs.sort = "YEAR(BIRTHDATE) DESC" but that doesn't work.  Is there a way to do this?
0
sgreenwood
Asked:
sgreenwood
  • 3
  • 3
  • 2
  • +3
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
What is the datatype of BirthDate ?

What is your database engine ?

0
 
sgreenwoodAuthor Commented:
The database is SQL Server 2000, BirthDate is a nvarchar field, does it need to be a date type to make this work?
0
 
sgreenwoodAuthor Commented:
The database is SQL Server 2000, BirthDate is a nvarchar field, does it need to be a date type to make this work?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Éric MoreauSenior .Net ConsultantCommented:
unless your date is formated as yyyy/mm/dd, sort will not work correctly. Change your SQL query to CAST you field as a datetime field.
0
 
bistricaCommented:
add extra column with year in it and sort it
0
 
SunnyJokerCommented:
With SQL Server you can use the function datepart(). In a Select-Statement it would look like:

select * from [tablename] order by datepart(year, BIRTHDATE)

You can also use month or day instead of year. I don't know if this works with rs.sort = "datepart(year, BIRTHDATE)". But if you can, always try to filter or sort direct within a Select-Statement and give the result to the recordset.
0
 
SunnyJokerCommented:
I forgot, like emoreau wrote it's recommendable to have a datetime field. Datepart casts the date in bithdate automatic to datetime, but if this doesn't work because  date in the field is incorrect you will receive an error. So best would be to change the type of the field permanent to a datetime format in the database.
0
 
vbbuffCommented:
dear sqreenwood

can you try this

 rs.sort = "clng(Cdate(BIRTHDATE)) DESC"
0
 
sgreenwoodAuthor Commented:
I tried both rs.sort = "datepart(year, BIRTHDATE)" and rs.sort = "clng(Cdate(BIRTHDATE)) DESC" with no luck.  Unfortunately, this recordset is from a stored procedure, and I have been told not to change that or the type of the field.  Thanks for the suggestions!
0
 
vbbuffCommented:
If you are opening the recordset by the execute method of the command object, then try using the open method of the recordset object.

for eg , If your statement is

set rs = com.execute

then try changing it to:

set rs = new Recordset
rs.cursorlocation = aduseclient
rs.open,com,adopenstatic,adlockreadonly,adcmdstoredProc

rs.sort = "clng(Cdate(BIRTHDATE)) DESC"
0
 
vbbuffCommented:
sorry it should be:

rs.open  "ProcName",com,adopenstatic,adlockreadonly,adcmdstoredProc
0
 
ayufansCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- answered by emoreau

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

ayufans
Cleanup Volunteer
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now