Inspiring

Answered

Process the result so that the dates are ordered by date

Forum|Forum|17 years ago
September 18, 2008
7 replies
757 views

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 🙂

Advanced techniques

This topic has been closed for replies.

Correct answer Newsgroup_User

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/

nikos101Author

Inspiring

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

nikos101Author

Inspiring

Thanks Azadi,

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

UPDATE staff
SET [dateAdded2] = dateAdded

-

-__cfSearching__-

Inspiring

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

N

Newsgroup_UserCorrect answer

Inspiring

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/

nikos101Author

Inspiring

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

A

A***

Inspiring

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>

D

Daverms

Inspiring

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

-

-__cfSearching__-

Inspiring

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

N

Newsgroup_User

Inspiring

store your dates as dates, not as text.

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

nikos101Author

Inspiring

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?

D

Dan_Bracuk

Inspiring

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded