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.
| Author |
Topic |
|
dmorand
Starting Member
20 Posts |
 Posted - 2007-09-24 : 09:23:22
|
I've got a table where the date and time fields are all out of sequence and I need them in sequence when I am extracting data from the table, but I'm having trouble writing the SQL. I've got this line which sorts the date and time for the table:SELECT * from tblcodegreydiv order by date desc, time desc Now I'm trying to retrieve the closest record prior to a date,time selected by a user...SELECT * from tblCodeGreyDiv WHERE recid=(SELECT MAX(recid) from tblCodeGreyDiv WHERE date + '' + time <= '2007-09-19 05:24') I need to somehow sort the table before I run the above sql statement. How would I go about doing this? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-24 : 09:27:25
|
1) Never split DATE and TIME.SELECT TOP 1 * from tblCodeGreyDiv WHERE date + ' ' + time <= '2007-09-19 05:24'ORDER BY date desc´, time desc
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
dmorand
Starting Member
20 Posts |
Posted - 2007-09-24 : 09:42:30
|
| I would have kept date,time together, but I didn't design the database, I'm creating some coldfusion pages from it. Thanks for the help though, that query did the trick!! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-09-24 : 13:48:35
|
quote:
Originally posted by Peso
1) Never split DATE and TIME.SELECT TOP 1 * from tblCodeGreyDiv WHERE date + ' ' + time <= '2007-09-19 05:24'ORDER BY date desc´, time desc
E 12°55'05.25"N 56°04'39.16"
You can split dates from times if you like, in fact in many cases it makes your code shorter, easier and much more efficient. however, you should use the correct data type -- datetime -- when doing so, in both columns.If you are using the correct datetime data type, you simply add -- not concatenate -- the date and the time to get back the datetime value:SELECT TOP 1 * from tblCodeGreyDiv WHERE date + time <= cast('2007-09-19 05:24' as datetime)ORDER BY date desc, time descmore here:http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
|
|
Sorting by date,time then select