Sorting - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Sorting

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-03-27 : 03:22:43

hi,

i need a query based on sorting.
i have date column and time as varchar in another column and other order details

datecol timcol ordername type
12/31/2007 1:00 pm order1 1
12/31/2007 5:00 pm order2 1
12/22/2007 6:00 pm order3 1

so i need to get query o/p as

12/22/2007 6:00pm
12/31/2007 1:00 pm
12/31/2007 5:00pm

based on date and time. pls help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-27 : 04:26:30

SELECT *
FROM Table1
ORDER BY CAST(DateCol AS DATETIME) + CAST(TimeCol AS DATETIME)

E 12°55'05.25"
N 56°04'39.16"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-03-27 : 04:51:02

Hello sir,
ur query works only when i set time as below
SELECT CAST('12:00PM' AS DATETIME)

and doesn't work for this case
SELECT CAST('12:00 P.M' AS DATETIME)

i usually store the record as '12:00 P.M' IN TIME COLUMN

Thanks for earlier post

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-27 : 05:20:39

OMG. Your approach is wrong on so many levels...

SELECT *
FROM Table1
ORDER BY CAST(DateCol AS DATETIME) + CAST(REPLACE(REPLACE(TimeCol, '.', ''), ' ', '') AS DATETIME)

E 12°55'05.25"
N 56°04'39.16"

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2008-03-27 : 07:19:25

Thanks a lot sir. it works fine now.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-27 : 08:58:35

dineshrajan_it -- ever consider the crazy idea of using the DateTime data type to store dates and times?

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 15:07:19