Process the result so that the dates are ordered by date

[nikos101]

I am retrieving a query result from a sql database of which one column contains a string of this format:

09/08/2008

How can I process this result so that the dates are ordered by date?

Thanks for anu help 🙂

[Newsgroup_User]

store your dates as dates, not as text.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[nikos101]

quote:

---

Originally posted by: Newsgroup User
store your dates as dates, not as text.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

---

What advantage is there in this, the string seems to work fine?

[Dan_Bracuk]

quote:

---

Originally posted by: nikos101

What advantage is there in this, the string seems to work fine?

---

You wouldn't have trouble sorting if you were storing dates as dates.
If you ever need to do date based queries, such as what happened last month, you can't.

[Newsgroup_User]

work fine? and that's why you were asking your original question, right?

dates are dates - date objects - that your db knows how to properly
handle when sorting, grouping, extracting a particular date part, etc etc.

strings are just text - your db has no idea what actual date it
represents. as you have found out, it won't even order your query
results as it would if your dates were dates.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Daverms]

Go through the cast functions of your SQL documentation and try to cast your string field as date after the order by clause.

[-__cfSearching__-]

CASTing the column to a datetime value should work. However, Azadi is correct. Dates should be stored as dates, not text.

[A***]

Set the data type to datetime and just order by as normal.

<cfquery name="query_name" datasource="data_source">
select …, …, …, date_string from table_name
order by date_string
</cfquery>
<cfoutput query=" query_name ">
#lsDateFormat(date_string, 'dd/mm/yyyy')#<br />
</cfoutput>

[nikos101]

I think I will convert it to datetime. How can I do this since I have many rows that contain the stringdates?

[Newsgroup_User · Accepted solution]

what's your db and version?
pretty much all dbs have built-in string and date functions, a
combinations of which will let you update your field to be date/datetime.

you will probably want to create a new field in the table to store the
converted dates, then check that they all have been converted correctly,
then delete the current date field and rename the new field to the old
date field's name.

using your db's string functions you will need to extract day, month and
year parts of your date text, and pass them to your db's date creation
function.

if your db is MySQL, it has a very handy STR_TO_DATE(str, format)
function... check details in mysql ref manual.



Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[nikos101]

Thanks Azadi,

I created a datetime column called dateAdded2 and run the following query:

UPDATE staff
SET [dateAdded2] = dateAdded

[-__cfSearching__-]

nikos101 wrote:
> I created a datetime column called dateAdded2 and run the following query:
> UPDATE staff
> SET [dateAdded2] = dateAdded

Unless you understand how your database handles string -> date conversions, it is better to use the approach Azadi mentioned. Use a function that tells the database exactly how it should interpret the string. For MS SQL use the convert function:

convert(datetime, yourColumn, format)

Some date strings are ambiguous. So if you do not tell the database the format of the value it is converting, it may interpret the date correctly .. or it may not.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

[nikos101]

Yes sorting datetime columns is now a dream for me!!