date range query syntax - 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

General SQL Server Forums

New to SQL Server Programming

date range query syntax

Author

Topic

MyronCope
Starting Member

46 Posts

Posted - 2008-01-22 : 07:44:40

hi, i'm using sql sserver 2005 and i have a time that i need to change to a datetime query (from the start of the day to the end of the day), whats the syntax for that? thx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-22 : 07:46:33

quote:
Originally posted by MyronCope

hi, i'm using sql sserver 2005 and i have a time that i need to change to a datetime query (from the start of the day to the end of the day), whats the syntax for that? thx

Is time currently stored in varchar field?

dineshpl2001
Starting Member

1 Post

Posted - 2008-01-22 : 07:57:49

you can use Convert syntax of SQL with 103 for dd/mm/yyyy format

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 07:59:33

Please show us your query you are using today.
Also provide information about what you want it to manage after the alteration.

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

MyronCope
Starting Member

46 Posts

Posted - 2008-01-22 : 08:02:16

Now, i'm only using query "datefield=date".
EX; "datefield='1/1/2008"
i am looking for the syntax for something like:

datefield IN (1/1/2008 12:00:00 AM, 1/1/2008 11:59:59 PM)

I want to return records in that range, but is that the right syntax? thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 08:03:41

If you want all records for a particular day, use

SELECT *
FROM   Table1
WHERE  DateField >= '20080101'    -- January 1, 2008
       AND DateField < '20080102' -- January 2, 2008

Your IN method only get all records which are EXACTLY midnight and just before.

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

MyronCope
Starting Member

46 Posts

Posted - 2008-01-22 : 08:04:35

also, do i need quotes around the datetimes?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 08:06:13

My previous suggestion is the correct one.
I think you ment BETWEEN, not IN before.

datefield IN BETWEEN '1/1/2008 12:00:00 AM' AND '1/1/2008 11:59:59 PM'

this will almost always work. But not always, due to the 3 ms incrementation in SQL Server 2005 and earlier versions.

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

MyronCope
Starting Member

46 Posts

Posted - 2008-01-22 : 08:08:42

THANKS for the reply.
i have to construct this query in the vb code, is it possible to use one operator, that would make it easier because i'm not sure how to construct using the current code with two ops.

Is there something that would query a range with one operator?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 08:14:54

Yes, the BETWEEN operator but then you have the problem with the milliseconds when they are very close to midnight next day.
Why do you not want a viable solution?

How can it be easier to code in Visual Basic a solution with one operator than with two operators?
You are still going to concatenate the query dynamically, right?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources