Sign in to follow this  

help writing SQL query

This topic is 4582 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi, I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL. Here's what I have so far:
select * from TICK_D
where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". Does anyone know how i could do this? Thanks a lot for any help!

Share this post


Link to post
Share on other sites
You could do two querys, somthing like this:

SELECT TOP 1 * FROM TICK_D WHERE datetime_traded >= yourdate ORDER BY datetime

and

SELECT TOP 1 * FROM TICK_D WHERE datetime_traded <= yourdate ORDER BY datetime DESC

Then in code just figure out which one is closest and use it.

Share this post


Link to post
Share on other sites
As always, I'll ask what database you are using (though from the looks of it it seems to be TSQL, so odds are it's SQL Server) to get an idea of what I can do. Also to be sure, I assume you are trying to do what TaskyZZ has proposed a solution for - given an arbitrary datetime value, find the closest datetime value in the table.

Share this post


Link to post
Share on other sites
Actually you already seem to already have a solution that mostly works, just two bugs:

'2005-05-30:09:31:09'
There shouldn't be a ":" between the date and the time for this to convert on a regular install of SQL Server (it should be a space, '2005-05-30 09:31:09', though string to date conversions can be fudgy if you mess with the system localizations, or input bad data [SQL Server can start acting like it's MySQL on some bad date data, deciding to use whatever format seems to match if the default doesn't])


datetime_traded = (select min( abs(datediff(...
This won't work. Datediff will return an integer value in seconds representing the time difference, which is obviously neither a valid datetime value, nor does it have any relation to the desired datetime value in the database.

Instead I would suggest something more like

SELECT TOP 1 * FROM TICK_D
ORDER BY ABS(DATEDIFF(second,datetime_traded,Convert(datetime,'2005-05-30 09:31:09'))

Share this post


Link to post
Share on other sites
I guess this should work:
SELECT *
FROM TICK_D
ORDER BY ABS(UNIX_TIMESTAMP('2005-05-30 09:31:09') - UNIX_TIMESTAMP(datetime_traded))
LIMIT 1

/Edit: I see you we're using MS SQL instead of MySQL. Ignore my solution.[smile]

Share this post


Link to post
Share on other sites

This topic is 4582 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this