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!
help writing SQL query
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:
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.
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.
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.
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'))
'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'))
I guess this should work:
/Edit: I see you we're using MS SQL instead of MySQL. Ignore my solution.[smile]
SELECT *FROM TICK_DORDER 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]
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement