Order by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order by

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2007-06-13 : 10:39:21

Hi all,
I am facing one problem with order by in a sql query statement.
Let me tell in brief:-
I have two fields in my table
Sent_DT (varchar as datatype)
Sent_Time (varchar as datatype)
These two fields are storing date and time respectivly
For eg:- Sent_DT = 30/01/2007
and Sent_Time = 04:02
Now I need to use order by for this two fields.
For this purpose I wrote one query
"select * from Event_Messages order by Sent_DT DESC, Sent_Time DESC"

But this query is not giving the correct answer. So is there any way to concordinate these two fiels or to convert it to datetime.

Please help me !

Shaji

sshelper
Posting Yak Master

216 Posts

Posted - 2007-06-13 : 10:41:26

You can merge the two fields and convert it to a datetime data type:

SELECT * FROM Event_Messages
ORDER BY CAST(Sent_Dt + ' ' + Sent_Time AS DATETIME) DESC

SQL Server Helper
http://www.sql-server-helper.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-13 : 10:41:45

You may need combine Sent_DT and Sent_Time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-13 : 10:41:53

don't use varchar for Sent_DT. Change the datatype of Sent_DT to datetime.

You can use convert() to convert the Sent_DT from varchar to datetime datatype.

Refer to BOL for more information on convert()

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-13 : 10:42:26

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-13 : 10:44:53

Where to get that icon?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-13 : 10:46:48

[ sniped ] without spaces

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-13 : 11:32:35

Always use proper datatype DATETIME to store dates
It is your front end where you need to show them seperately

Madhivanan

Failing to plan is Planning to fail

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2007-06-13 : 12:11:31

Now changing datatype from varchar to datetime is not so easy. B'cose allready done some other kind of application based on this field

Shaji

quote:
Originally posted by madhivanan

Always use proper datatype DATETIME to store dates
It is your front end where you need to show them seperately

Madhivanan

Failing to plan is Planning to fail

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2007-06-14 : 02:21:25

Thanks,

But the given below query is giving an error like :
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

shaji

quote:
Originally posted by sshelper

You can merge the two fields and convert it to a datetime data type:

SELECT * FROM Event_Messages
ORDER BY CAST(Sent_Dt + ' ' + Sent_Time AS DATETIME) DESC

SQL Server Helper
http://www.sql-server-helper.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 02:33:13

use

convert(datetime, Sent_Dt + ' ' + Sent_Time, 103)

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2007-06-14 : 02:58:56

Thanks Mr.Khtan,

Its working fine.

Shaji

quote:
Originally posted by khtan

use
convert(datetime, Sent_Dt + ' ' + Sent_Time, 103)

[font=Comic Sans MS][size=1]KH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-14 : 08:53:13

What is the format you are storing dates in varchar column?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources