very annoying DATES

[benbacardi]

i have two date and time problems!!

on this simple forum i have written, i have it storing the lastupdated date in a field, and ordering the results when i pull them from the database by lastupdated DESC - this should produce the desired result of latest changed at the top, right?! well it did, until today - any posts added on the 13/05/2005 are chucked after stuff on the 10-12th 2005 - which i do not understand. all dates are in the same format.....

and how can i get dates to go in English time? at the moment, i have session.LCID or whatever it is set to english, but occasionally, the date gets printed in american... how can i solve that?

[D3mon]

Most likely that database that you are using is incorrectly configured to US (usually the default) or is hosted in the States

If you're using MS Access or SQL than you might try CONVERTing or CASTing the date to a UK format before specifiying the sort order.

I'd advise whenever working with dates between a webpage and a databse to always supply them in this format:

dd monthname yyyy

ex. 13 May 2005.

This method will store the correct date in the database independently of its configuration. Similalry when oulling the dates from the database, as mentioned before, get the converted to the correct format first by the query.

[benbacardi]

but how can i use a convert method in a SELECT statement?

also, the database stores it in the correct way of dd/mm/yyyy hh:ss so why does it not order it properly?

[D3mon]

MS Access or MS SQL database?

[benbacardi]

MS Access but using SQL to query it

[benbacardi]

woo sorted

[D3mon]

both use slightly different SQL.

The Format function is what you'll need for MS Access:

http://www.techonthenet.com/access/f...ate/format.htm

[benbacardi]

can anyone explain to me why i get data type mismatch in criteria expression on the following line:

set rs = conn.Execute("SELECT * FROM ftopics2 ORDER BY cdate(lastupdated) DESC")

lastupdated is a text field with the format "14 May 2005 12:24" in it....

[benbacardi]

ok this is making no sense whatsoever!!!! it works for SQL statements but not for others...

[Trebz]

CDate() is a VBScript Function


In SQL you need to do CONVERT(datetime, lastupdated)

I don't know why you are not just storing your date/time data in a datetime field?

No matter what the setup of SQL, it'll still be a valid date, you just pull it out and reformat it in your vbscript.

[benbacardi]

storing it as a date didnt work for some reason it always displayed them in a funny order.

i get this error now: undefined function CONVERT in expression....

[Trebz]

Sorry, didn't read some of those posts inbetween, CONVERT is a MSSQL function, won't work in Access.

I still dont think it should matter what country your dates are in, you should still be able to CDate in VBScript and then FormatDateTime to the way you want them, or even do Day(Date) MonthName(Month(Date)) Year(Date) etc... to extract the proper parts of the field in the proper order.

[benbacardi]

its not the storing of the dates that is the problem - its displaying them. it doesnt display them in the correct order whatever i do....

[Trebz]

Store the dates as dates int he database, and when you pull them out, put them through this function

Code: Select all

Function fnFixAccessDates(dtmDate)
  
  Dim strRetVal
  
  strRetVal = Day(dtmDate) & "/" & MonthName(Month(dtmDate)) & "/" & Year(dtmDate) & " " & Hour(dtmDate) & ":" & Minute(dtmDate) & ":" & second(dtmDate)
  
  fnFixAccessDates = strRetVal
  
End Function

[benbacardi]

it still doesnt store them in the correct order though... posts on the 13th may come after posts on the 11th which they shouldnt....

[Trebz]

What does your query look like?

You ought to have

Code: Select all

SELECT
	Col1,
	Col2,
	etc...
FROM dbo.MyTableName
ORDER BY myDateCol DESC

Which will give you your data sorted by date with the newest first.