Query using DateTime - 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)

Query using DateTime

Author

Topic

newbiePHP
Starting Member

10 Posts

Posted - 2007-10-22 : 13:22:07

Hi Guys,

SELECT *
FROM `Example`
WHERE `DateTime` = "2007-08-28 15:24:44"

My Question is if this entry doesn't exist in the database how do I find the closest one in terms of time?

For example if there is an entry 2 seconds before this one or 5 seconds after, I want the query to return the closest one, which in this case would be the one 2 seconds before.

DateTime column is of course of type DateTime.

Any help would be really appreciated.

thanks,
newbie

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-10-22 : 13:33:44

create table #Example (
MyDateTime Datetime
)

insert into #Example values ('2007-08-28 15:24:42')
insert into #Example values ('2007-08-28 15:24:49')

select top 1 abs(DateDiff(ss, MyDateTime, '2007-08-28 15:24:45')), *
from #Example
ORDER by abs(Cast(DateDiff(ss, MyDateTime, '2007-08-28 15:24:45') as int)) ASC

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-22 : 13:35:57

DECLARE @date DATETIME
SET @date = '2007-10-02 13:34:04.864'

SELECT TOP 1 <datefield>
FROM 'Example'
ORDER BY ABS(DATEDIFF(ss,@date,<datefield>)), <datefield>

Future guru in the making.

newbiePHP
Starting Member

10 Posts

Posted - 2007-10-22 : 13:43:47

I must be doing this wrong as I keep getting errors.

I'm connected to the database and tried running

select top 1 abs(DateDiff(ss, MyDateTime, '2007-08-28 15:24:45')), *
from #Example
ORDER by abs(Cast(DateDiff(ss, MyDateTime, '2007-08-28 15:24:45') as int)) ASC

but it just gave me an error.

Zoroaster,

do i put 2007-08-28 15:24:45 where datafield is?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-10-22 : 13:49:08

What Error would be helpfull

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-22 : 13:49:53

quote:
Originally posted by newbiePHP

Zoroaster,

do i put 2007-08-28 15:24:45 where datafield is?

No, datefield would be you column name containing the datetime value.

Future guru in the making.

newbiePHP
Starting Member

10 Posts

Posted - 2007-10-22 : 13:51:46

the error is #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

I am using PHPMyadmin and just using SQL query to query the table directly if that helps

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-22 : 13:53:31

CREATE TABLE date (datevalue DATETIME)

INSERT INTO date VALUES ('2007-08-28 15:24:45')
INSERT INTO date VALUES ('2007-08-28 15:23:46')

DECLARE @date DATETIME
SET @date = '2007-08-28 15:24:00'

SELECT TOP 1 datevalue
FROM date
ORDER BY ABS(DATEDIFF(ss,@date,datevalue)), datevalue

Future guru in the making.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-22 : 13:54:07

quote:
Originally posted by newbiePHP

the error is #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

I am using PHPMyadmin and just using SQL query to query the table directly if that helps

MySQL doesn't support TOP, this is a sql server forum so that is why it is failing on syntax.

Future guru in the making.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-10-22 : 13:55:45

in MySQL I don't know. The syntax is Correct for SQL Server.
Look for the same functions in MySQL and get the syntax.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-22 : 13:56:03

For MySQL you can do this:

DECLARE @date DATETIME
SET @date = '2007-08-28 15:24:00'

SELECT datevalue
FROM date
WHERE datevalue =
(SELECT MIN(datevalue)
FROM date
WHERE ABS(DATEDIFF(ss,@date,datevalue))=
(SELECT MIN(ABS(DATEDIFF(ss,@date,datevalue)))
FROM date))

Future guru in the making.

newbiePHP
Starting Member

10 Posts

Posted - 2007-10-22 : 14:04:30

Maybe I should clarify.

I'm using PHPMyadmin to connect to MYSQL database. The table name is Example and the are 15 columns in it. One of the columns name is 'DateTime'. It's of the format '2007-08-28 15:24:44'.

I need to query this database to find the exact DateTime I send it OR if that entry doesn't exist to find the closest one.

So if I say return the row with datetime = 2007-08-28 15:24:44. it returns that row or looks through the rest of the table for the closest one which might be 2007-08-28 15:24:52.

Zoroaster,
given this information could you right out the exact query cause I don't think I understand the syntax ?

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-22 : 14:36:26

[code]
DECLARE @date DATETIME
SET @date = '2007-08-28 15:24:44' -- This is where you put the value you want to evaluate

SELECT [datetime]
FROM example
WHERE [datetime] =
(SELECT MIN([datetime])
FROM example
WHERE ABS(DATEDIFF(ss,@date,[datetime]))=
(SELECT MIN(ABS(DATEDIFF(ss,@date,[datetime])))
FROM example))

[/code]

Future guru in the making.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-22 : 16:08:24

newbiePHP -- you have asked your questions on a Microsoft SQL Server website and you chose the SQL Server 2005 forums, which is for a specific edition of SQL Server. You should go to a PHP or MySQL website with your questions, it is a completely different product and the dialects of SQL are very different.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 01:41:26

MySQL supports TOP but has another syntax.
Read about LIMIT in your help files.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 01:48:11

quote:

Originally posted by Zoroaster


DECLARE @date DATETIME
SET @date = '2007-08-28 15:24:44' -- This is where you put the value you want to evaluate


SELECT [datetime]
FROM example
WHERE [datetime] =
(SELECT MIN([datetime])
FROM example
WHERE ABS(DATEDIFF(ss,@date,[datetime]))=
(SELECT MIN(ABS(DATEDIFF(ss,@date,[datetime])))
FROM example))

Future guru in the making.

You dont need variable declaration in Mysql

SET @date:= '2007-08-28 15:24:44'

would work fine

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources