Big Problems

I've come across an issue which i believe is related to Windows 2003 populating database elements, notably date/time types, and having those fields read on another platform (eg. winXP, win98) and, although everything would seem to match, comparissons fail.

For example, if i populate a datetime field with the value 22:22 (eg. 10:22 pm) from a Windows 2003 pc, then read the value of the field on another pc (eg. WinXP), and perform a comparisson in code (vb6sp5), the values are not equal.

I have observed this against Access database using DAO 2.5, 3.51 and 3.6 on both sides. Also observed against SQL Server 2000.

All i can figure is that windows 2003 is causing the problem, but i don't know how to resolve it.

I understand this is not a forum for operating system questions, but my intent would be to generate a routine which would correctly populate the database elements on the win2003 side, for proper execution on the other pcs in use.

Any help would be greatly appreciated.Do you perform your comparison in code or visually? I had to use my 2003 with client tools against 2K SQL Server and had no prob. If you're doing visual comparison then you need to also account for regional settings.|||visually everything looks the same
in code, i've compared stored results vs expected results. here's a sample of what i found:

If i stored, from the Win2003 pc, the value 0:01 (12:01am, no date) in either an access or sql server field, then attempted from a winxp pro machine to compare the stored number to winxp's interpretation of 12:01 am, the values would look to be the same, but vb would not claim them to be equal. if i check the difference between the two, surprisingly, the difference is 1.0842021724855E-19, and that's enough to cause this symptom. the difference fluctuates, with 12:23 am working and being equal when the above steps are followed. the pattern of equal/not equal is very strange indeed.

i have verified regional settings on both pcs. no problems.

this all works fine with all pc's being win2003. it seems that 2003 is treating something different, so comparisons to a value stored from the win2003, then compared on another os, it all fails.|||This is not a SQL issue. It's a VB issue. I dont get any problems like this in VB.NET. It's been so long for VB6 and I dont really feel like installing it so I wont be able to do any VB6 testing. Have you tried using VB.NET instead? I'm sure you'll be cursing at VB6 in no time once you switch to .NET. :D

Also what version of Access? 97, 2k?|||hahahaha

oh yeah, i've used vb.net and it's a much better development platform for sure. i will test this scenario in vb.net and see what the results are.

unfortunetly, i am supporting legacy applications and bumping up to vb.net does not give me the immediate solution i require.

has nobody else observed this behaviour?

access 97 and 2000
sql server 2000 (msde and enterprise) v8.00.760

Big Int to Date Conversion

Hi Guys!!!
i really need your help now. i tried my level best to make some
tricks on this problem but didn't make it.
i've got a Big Int datatype column contains the "Ticks" (Long value)
and i want to convert it into DateTime but i'm not able to do it. the
value is like this 632557193645506250 i want to convert it into
datetime format.
please help me out guys. i'm using ms sql server 2000.
and front end is vs .net 2005
i want to do it into sql Query please dont ask me the reason but
because of some constraints i can not do it in .net
PLEASE HELP ME GUYS.
hi,
Lucky wrote:
> Hi Guys!!!
> i really need your help now. i tried my level best to make some
> tricks on this problem but didn't make it.
> i've got a Big Int datatype column contains the "Ticks" (Long value)
> and i want to convert it into DateTime but i'm not able to do it. the
> value is like this 632557193645506250 i want to convert it into
> datetime format.
> please help me out guys. i'm using ms sql server 2000.
> and front end is vs .net 2005
> i want to do it into sql Query please dont ask me the reason but
> because of some constraints i can not do it in .net
>
> PLEASE HELP ME GUYS.
DATEDIFF() and DATEADD() builtin functions expect a parameter of the "int"
datatype... id you perform
SET NOCOUNT ON;
DECLARE @.d datetime, @.t bigint;
SET @.t = 632557193645506250;
SELECT @.d = DATEADD (ms, @.t, '19000101');
SELECT @.d AS [date as difference in ms from 01/01/1900];
which adds the specified num of ticks to a base data value (you are required
to know in order to perform calculation), you get
Server: Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.
date as difference in ms from 01/01/1900
NULL
as the provided "difference" exceeds the max supported value...
http://msdn.microsoft.com/library/de...da-db_3vtw.asp
if you can "convert" that big value into separated values as "days" for the
date part and "ms" (depending on the actual precision you are required to
provide) for the time part in order not to overflow the int datatype
(32767), you can easely use the DATEADD(..) function..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 4 Apr 2006 19:18:30 +0200, Andrea Montanari wrote:

>if you can "convert" that big value into separated values as "days" for the
>date part and "ms" (depending on the actual precision you are required to
>provide) for the time part in order not to overflow the int datatype
>(32767), you can easely use the DATEADD(..) function..
Hi Andrea,
Eh? 32767 ?
I think you meant to write 2,147,483,647 here...
To Lucky:[vbcol=seagreen]
Can you tell me how long a "tick" lasts, and what the starting point for
the meaasurement is? And what is the exact date and time corresponding
to the 632557193645506250 value in your post?
Hugo Kornelis, SQL Server MVP
|||hi Hugo
Hugo Kornelis wrote:
> On Tue, 4 Apr 2006 19:18:30 +0200, Andrea Montanari wrote:
>
> Hi Andrea,
> Eh? 32767 ?
> I think you meant to write 2,147,483,647 here...
oopsss.. just working with vb6 these days and the relative "int" dimension..
LOL.. I apologise ..
thank you for poiting it out..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

BETWEEN, when given a date for a datetime field

BETWEEN '20070601' AND '20070630'
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
--
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatype conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
--
Aaron Bertrand
SQL Server MVP|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that "June" was what
metaperl wanted, and for some reason this was transferred to me writing that incorrect value. Thanks
for catching it. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
>> BETWEEN '20070601' AND '20070630'
>> The strings will be converted to datetime values (thanks to implicit datatype conversion):
>> 20070601 00:00:00 to 20070701 00:00:00
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>

BETWEEN, when given a date for a datetime field

BETWEEN '20070601' AND '20070630'
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00
The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatype conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
Aaron Bertrand
SQL Server MVP
|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that "June" was what
metaperl wanted, and for some reason this was transferred to me writing that incorrect value. Thanks
for catching it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>

BETWEEN, when given a date for a datetime field

BETWEEN '20070601' AND '20070630'
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatyp
e conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored
datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
Aaron Bertrand
SQL Server MVP|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that
"June" was what
metaperl wanted, and for some reason this was transferred to me writing that
incorrect value. Thanks
for catching it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>

BETWEEN syntax at SQL

Hi,

I would like to know how MSSQL handles dates for BETWEEN syntax at SQL statements.

e.g. SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND '2003/12/31'

I find that the result rows do not include ones with [date]='2003/12/31'

So, now I'm using BETWEEN '2003/01/01' AND '2004/01/01' instead.

Is it a standard way to perform this kind of task?between ... and ... is equal >= and <=
So I think u can not get the data that date column value is bigger than 2003/12/31 00:00:00

SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND '2003/12/31'

must change to ==>

SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND dateadd(day,1,'2003/12/31')|||enhydraboy is right with the date time of '2003/12/31 00:00:00'
This is where your between will have a problem when comparing
'2003/12/31' against lets say '2003/12/31 12:45:30'

some times I just force it to '2003/12/31 23:59:59'
when I use date and time : )

Hope this helps|||Originally posted by TrueCodePoet
enhydraboy is right with the date time of '2003/12/31 00:00:00'
This is where your between will have a problem when comparing
'2003/12/31' against lets say '2003/12/31 12:45:30'

some times I just force it to '2003/12/31 23:59:59'
when I use date and time : )

Hope this helps

Well ...
The standard i would use is

SELECT * FROM [sysobjects] WHERE convert(datetime,convert(varchar(10),[crdate],121)) BETWEEN '2003/01/01' AND '2003/12/31'|||Originally posted by Enigma
Well ...
The standard i would use is

SELECT * FROM [sysobjects] WHERE convert(datetime,convert(varchar(10),[crdate],121)) BETWEEN '2003/01/01' AND '2003/12/31'

That is, quite frankly, bonkers. You are forcing a column conversion on every row in the table.

Would it not make a more sargeable filter to convert the between varchars to the date format required? It happens once in the excution of the query and applies to all rows with no further conversion.

And if he wants to include all of the last day of the month, it's

BETWEEN '1 Jan 2004' AND '1 Feb 2004'|||Originally posted by HanafiH
That is, quite frankly, bonkers. You are forcing a column conversion on every row in the table.

Would it not make a more sargeable filter to convert the between varchars to the date format required? It happens once in the excution of the query and applies to all rows with no further conversion.

And if he wants to include all of the last day of the month, it's

BETWEEN '1 Jan 2004' AND '1 Feb 2004'

Never did I think of it that way ... and believe me .. I have been using it like this for past two years ... thanks for correcting me ... you have been a great help :)

Well .. you learn at least one new thing on this site everyday|||Writing International Transact-SQL Statements (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_3unn.asp)
A possible solution to this is to use the ISO Standard format for sending the datetime data to SQL Server, which is "YYYYMMDD" (no separators). Using the ISO format is more "international," and is independent of the default language. For more information, see the CONVERT function in the SQL Server Books Online.|||It makes no difference what format the datetime value is sent in. It does make a difference whether the datetime value includes a time portion or not. Many applications cannot restrict themselves to whole date values, and thus some sort of conversion or adjustment is necessary to include all events occuring on the last day.|||Originally posted by buser
A possible solution to this is to use the ISO Standard format for sending the datetime data to SQL Server, which is "YYYYMMDD" (no separators). excellent advice

one minor point: ISO standard does include separators

see Numeric representation of Dates and Time (http://www.iso.ch/iso/en/prods-services/popstds/datesandtime.html)|||Sorry.. I might have been confusing .. I will just post what I usualy do.

SELECT * FROM [sysobjects] WHERE [crdate]
BETWEEN
convert(datetime,'2003/01/01 00:00:00',120)
AND
convert(datetime,'2003/12/31 23:59:59',120)

the two functions should only be run once durring the compile
: )

I have not noticed much difference with this query on 1 mill plus records
Only reasonI don't use the 121 format is the Milleseconds is not needed for me Most of the Time I use the 101 format.

hope this helps|||SELECT * FROM [sysobjects] WHERE [crdate]
BETWEEN
convert(datetime,'2003/01/01 00:00:00',120)
AND
convert(datetime,'2003/12/31 23:59:59',120)

actually you don't have to convert them

when you put the year first like that, the database will assume that the next field is the month, etc.

SELECT * FROM [sysobjects]
WHERE [crdate]
BETWEEN '2003/01/01 00:00:00'
AND '2003/12/31 23:59:59'

BETWEEN predicate with passed parameters

I have a report in SQL that passes parameters at runtime entered by the user for two date ranges (beginning and ending). I'm trying to write a formula that will print a specific field *only if* the specified date range entered by the user is BETWEEN a specific value (like 200401). This is kind of reverse of a normal WHERE, BETWEEN clause.

I tried a standard BETWEEN predicate in my WHERE clause like:
IF '200401' BETWEEN ?BegPer and ?EndPer then salesanal.ptdbud01 else 0

But, it's returning an error that my Then statement is missing. I can't use a normal statement like 'IF ?BegPer >= '200401' and ?EndPer <= '200401', then....' because users could enter a RANGE of periods, so it would be difficult to code all of the possible combinations this way. I'm actually doing this in Crystal, but if someone can give me a standard MSSQL example, I can translate that over to Crystal.

Thanks in advance,
MichelleUSE Northwind
GO

SELECT *
FROM Orders
WHERE OrderDate >= '1996-07-08 00:00:00.000'
AND ShippedDate <= '1996-07-15 23:59:59.000'
GO|||Yeah, I can't just use the standard WHERE clause with >= and <=. I need to return a separate value for each month of the calendar year (these are setup in separate fields on my report). So, if the user enters parameter values into begper = '200401' and endper = '200403', then the report needs to print all 3 fields for months 01, 02, and 03, because they all fall within the range of 200401 and 200403. I would have to code all possible combos of a begper >= 'xx' and endper <= 'yy', ya know? While that may be entertaining, I wonder if there's a more efficient route? <g>

More details:
Table:
Custid, Janbudget, Febbudget, Marbudget, Aprbudget, etc etc.
User enters runtime parameters for a monthly period range, such as between '200401' (January) and '200403' (March). In this case, my report should only print the values in fields Janbudget, Febbudget and Marbudget. All other fields will either not print or print 0.

Any other ideas?

Thanks!
Michelle :)

Originally posted by Brett Kaiser

USE Northwind
GO

SELECT *
FROM Orders
WHERE OrderDate >= '1996-07-08 00:00:00.000'
AND ShippedDate <= '1996-07-15 23:59:59.000'
GO|||You need to use Group BY and use scalar functions...hold on...|||Like this?

SELECT OrderId
, DATEPART(yy,OrderDate) AS OrderDate_yy
, DATEPART(mm,OrderDate) AS OrderDate_mm
, DATEPART(yy,ShippedDate) AS ShippedDate_yy
, DATEPART(mm,ShippedDate) AS ShippedDate_mm
, COUNT(*) AS Orders
FROM Orders
WHERE OrderDate >= '1996-07-08 00:00:00.000'
AND ShippedDate <= '1996-07-15 23:59:59.000'
GROUP BY OrderId
, DATEPART(yy,OrderDate)
, DATEPART(mm,OrderDate)
, DATEPART(yy,ShippedDate)
, DATEPART(mm,ShippedDate)

between operator

Hi,

I'm having problems using the between operator in a query statement.

Select *
from <mytable>
where date between @.date1 and @.date2

The date values with a hour specified, aren't returned. What is the approach you would recommend here?

Thx

EDIT: by playing with this problem I've figured out I can append the hour to the date like this: <date> between @.fromDate + '00:00:00' and @.toDate + '23:59:59'

this seem to work, but I'm not sure if this is correct

Select *
from <mytable>
where date >= @.date1 and date < dateadd(d,1,@.date2)

or

Select *
from <mytable>
where date >= @.date1 and date < @.date2 +1

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

i had a similar problem which i did through the application side:

string weekdate =txtFrom.Text;

string daydate = System.Convert.ToDateTime(txtTo.Text).Add(System.TimeSpan.FromDays(1)).ToString();

and later...

TechnicianViewInboxDataAdapter.SelectCommand.CommandText = tempselecttechnician + " where status = 1 and TechnicianID = " + Session["UserID"].ToString() + "and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' and DepartmentID = " + Session["DepartmentID"].ToString();

this works as well.

the code ends up like this:

SELECT FirstName, LastName, Email, CallNumber, DepartmentName, UserName, Status, TechnicianID, CallLoggedDT, DepartmentID, CallOpenedDT, CallActionedDT, CallClosedDT FROM dbo.TechnicianInboxView where status = 1 and TechnicianID = 2and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' and DepartmentID = 2

|||

You can also use the datediff function and the statement would look like this.

Select * from <mytable> where

datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0

@.Date can be varchar too and it can be in the format of "mm/dd/yy" also. Need not have the time factor.

|||

where datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0

Would not recommend doing so in the terms of performace. You will never get index seek over the index for datefield column: it will always index scan at the best.

BETWEEN keyword

I need to retrieve records where the date is in between the current date and 4 days previous.

I've tried: WHERE DateSubmitted BETWEEN GetDate() AND GetDate() - 4
it doesn't work...

Can someone help out?You are probably having problems because both the date and the time are used in such comparisons.

See if this post helps you:view post 483129

Terri|||Ok, thanks Terri.

between dates and times

Hello,
I am new to SQL and am having a problem with dates and times.
I have a table with a 4 columns. Date, Time, Material, Qty
example;
01/12/05 04:00 steel 35
01/12/05 06:00 steel 60
01/12/05 23:40 steel 40
01/12/05 08:00 iron 25
02/12/05 05:00 steel 20
02/12/05 06:00 steel 50
I want to be able to look for matching rows that have a start & end date and
start & end time.
i.e start date 01/12/05 time >= 06:00
end date 02/12/05 time <= 05:59
The times are aways static. I ideally would like to see one row returned tha
t
summed the last column and combined the different dates like;
01/12/05 steel 120
01/12/05 iron 25
however if I searched for 01/12/05 and 03/12/05 I would see;
01/12/05 steel 120
01/12/05 iron 25
02/12/05 steel 50
Any help or guidance will be appreciated.What are the datatypes of the date and time columns. The ideal solution
would be to represent them as a single datetime value.
In any case, as a short term solution, check out the CONVERT function in SQL
Server Books Onlne. There are arguments that can help you convert a datetime
value to date-only string and a time-only string.
Anith

between dates

in sql2000
"select * from tbl where tbl.date between date1 and date2" - results with
nothing
"select * from tbl where tbl.date >= date1 " - gives result
"select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
with nothing
how to select between 2 dates?
thanksHow about providing us with real specs, e.g. are the columns really
datetime/smalldatetime? What data is in them? What values are you using
for date1 and date2? What format are they in?
Please see http://www.aspfaq.com/5006 ... otherwise vague questions will
only get vague answers.
"Sam" <focus10@.zahav.net.il> wrote in message
news:ey3GNgirFHA.248@.TK2MSFTNGP14.phx.gbl...
> in sql2000
> "select * from tbl where tbl.date between date1 and date2" - results with
> nothing
> "select * from tbl where tbl.date >= date1 " - gives result
> "select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
> with nothing
> how to select between 2 dates?
> thanks
>
>|||column date is datetime type
the values are simple:
"select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
gives NO RESULT
"select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
"select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives NO
RESULT
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
> How about providing us with real specs, e.g. are the columns really
> datetime/smalldatetime? What data is in them? What values are you using
> for date1 and date2? What format are they in?
> Please see http://www.aspfaq.com/5006 ... otherwise vague questions will
> only get vague answers.
>
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:ey3GNgirFHA.248@.TK2MSFTNGP14.phx.gbl...
>|||See point number 3 in the first link.
Should I use BETWEEN in my database queries?
http://www.aspfaq.com/show.asp?id=2280
The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp
AMB
"Sam" wrote:

> column date is datetime type
> the values are simple:
> "select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
> gives NO RESULT
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
> "select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives
NO
> RESULT
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
>
>|||If u r using date1 and date2 as parameters, in ur case date2 must be null.
Even if one of the dates is null, BETWEEN or simple comparison will fail as
happening in ur cases.
pls chk and let me know in case of any dbts
Rakesh
"Sam" wrote:

> in sql2000
> "select * from tbl where tbl.date between date1 and date2" - results with
> nothing
> "select * from tbl where tbl.date >= date1 " - gives result
> "select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
> with nothing
> how to select between 2 dates?
> thanks
>
>|||(a) you need to delimit datetime values with '
(b) never use ambiguous and silly formats like m/d/y or d/m/y. Is
01/05/2005 January 5th or May 1st? Who knows?
Try:
WHERE date >= '20020101' AND date <= '20050105'
"Sam" <focus10@.zahav.net.il> wrote in message
news:ehA02sirFHA.2592@.TK2MSFTNGP09.phx.gbl...
> column date is datetime type
> the values are simple:
> "select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
> gives NO RESULT
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
> "select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives
> NO RESULT
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
>|||
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
What are the date values for these results? Are they all later than
"01/05/2005"?
Perayu|||i found the solution:
@.date1 nvarchar(12), @.date2 nvarchar(12),
'BETWEEN CONVERT(DATETIME, ' + char(39) + @.date1 + char(39) + ', 102) AND
CONVERT(DATETIME, '+ char(39) + @.date2 + char(39) +', 102)'
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:790C5553-6B88-45F8-A1D6-4F32CF001010@.microsoft.com...
> If u r using date1 and date2 as parameters, in ur case date2 must be null.
> Even if one of the dates is null, BETWEEN or simple comparison will fail
> as
> happening in ur cases.
> pls chk and let me know in case of any dbts
> Rakesh
> "Sam" wrote:
>

Between date selection

Hi,

I have the following situation.

I want to create reports with a start date parameter and an end date parameter

In my query i use "where date between @.startdate and @.enddate"

i configure my parameters so i get a nice calendar when i generate the report.

the problem is when i select for example a date starting 1 april 2007 and enddate 10 april 2007,

the result in my report gives me only the data from 1 until 9 april and not until 10.

in my database the date is stored as a date time (10/04/2007 17:25:30).

Any suggestion how i can solve my problem?

Greetings

Vinnie

Hello Vinnie,

The problem is that your database stores the time with your date and when you select a date in the date picker, it doesn't have a tme, so anything after midnight gets excluded. Try this as your where clause:

where date >= @.startdate and date < dateadd(d, 1, @.enddate)

Hope this helps.

Jarret

|||

Hi Vinnie,

When I come across this I normally use a conversion in my query:

where convert(varchar(25),date,101) between @.startdate and @.enddate

The conversion eliminates the time from the database field and will pull back all rows with the date portion between the start and end dates.

Simone

|||

Do you have a date picker control on your reporting website, so that instead of the user having to manually input the date in the correct format, they are given a control to pick the date ranges?

(Like datetimepicker control in C#)

Please let me know as that is what I need to do.

|||

Yes I do. You need to change the data type of the parameter in the Report to datetime. Go to "Report" then "Report Parameters" Select the parameter from the list on the left and change the data type to DateTime. This should automatically give the date control.

Simone

Between Date Query

How would I make a query that checks for all dates using a feature that
automatically selects todays date and then 14 days in the future. Meaning
all records between today and 14 days in the future.
Thanks.The SQL should read something like:
SELECT *
FROM MyTable
WHERE MyDateField BETWEEN GETDATE() AND DATEADD(Day, 14, GETDATE())
HTH,
Magendo_man
"Samson" wrote:
> How would I make a query that checks for all dates using a feature that
> automatically selects todays date and then 14 days in the future. Meaning
> all records between today and 14 days in the future.
> Thanks.|||thank you. it worked
"magendo_man" wrote:
> The SQL should read something like:
> SELECT *
> FROM MyTable
> WHERE MyDateField BETWEEN GETDATE() AND DATEADD(Day, 14, GETDATE())
> HTH,
> Magendo_man
> "Samson" wrote:
> > How would I make a query that checks for all dates using a feature that
> > automatically selects todays date and then 14 days in the future. Meaning
> > all records between today and 14 days in the future.
> >
> > Thanks.

Between date problem

I have a SP that gets two dates passed to it. Those dates are then used to
pull records created between those dates. The problem is that the created
date is a timestamp and so when I pass 3/15/2006 and 3/15/2006 no rows are
returned. If I want today's records I have to pass 3/15/2006 and 3/16/2006.
This is my where clause WHERE CONVERT(VARCHAR(10),Created_TS,112) BETWEEN
@.strFromDate AND @.strToDate How can I fix this? Thanks.Don't use BETWEEN. And don't pass in a string in that format, use YYYYMMDD
and SMALLDATETIME parameters. Don't do any string conversions of the value
in the table. This will basically destroy performance and doesn't do a
whole lot for readability of the query, either. Finally, pass in the day
*after* the end of the range. So, for 3/15 -> 3/15, pass in 3/15 and 3/16.
Then, say:
CREATE PROCEDURE dbo.foo
@.rangeStart SMALLDATETIME,
@.rangeEnd SMALLDATETIME
AS
BEGIN
SET NOCOUNT ON;
SELECT ...
WHERE Created_TS >= @.rangeStart
AND Created_TS < @.rangeEnd;
END
GO
e.g.
EXEC dbo.foo @.rangeStart = '20060315', @.rangeEnd = '20060316';
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:2A9229F5-B2D5-4830-99D1-2C57FAF75D3C@.microsoft.com...
>I have a SP that gets two dates passed to it. Those dates are then used to
> pull records created between those dates. The problem is that the created
> date is a timestamp and so when I pass 3/15/2006 and 3/15/2006 no rows are
> returned. If I want today's records I have to pass 3/15/2006 and
> 3/16/2006.
> This is my where clause WHERE CONVERT(VARCHAR(10),Created_TS,112) BETWEEN
> @.strFromDate AND @.strToDate How can I fix this? Thanks.

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
--
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
> > Hai,
> >
> > I have a table with datetime type column. I stored the date with time in
> > this column.
> > Now i want to select the records between 10/01/2005 and 10/31/2005.
> >
> > I used 'select * from tablename where columnname between '10/01/2005' and
> > '10/31/2005'' query to select records.
> >
> > But the above query returns upto 10/30/2005.
> >
> > Please advise me.
> >
> > Rgds,
> > Soura
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> > select *
> > from tablename
> > where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>> Soura,
>> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
>> after midnight will not be included in the results.
>> Try:
>> select *
>> from tablename
>> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
>> Rob
>> SouRa wrote:
>> > Hai,
>> >
>> > I have a table with datetime type column. I stored the date with time in
>> > this column.
>> > Now i want to select the records between 10/01/2005 and 10/31/2005.
>> >
>> > I used 'select * from tablename where columnname between '10/01/2005' and
>> > '10/31/2005'' query to select records.
>> >
>> > But the above query returns upto 10/30/2005.
>> >
>> > Please advise me.
>> >
>> > Rgds,
>> > Soura

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
Soura
This works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]
|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:

> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>
|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:

> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>
|||Did you read my article?

> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/in...fo_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:

> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:

> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?

> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be dis
astrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each tim
e.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>

Between [Enter the Start Date] And [Enter the End Date])

Hi all can you help me please, I need the users to be able to enter in a date for a self generating report. Usually I use the @.Enter_Date but I need them to be able to enter a start date and a end date
But when I run this query SQL gives me an error message Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Enter the Start Date'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Enter the End Date'.

What should use in place of BETWEEN [Enter the Start Date] And [Enter the End Date]??

SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]Is it possible for your front end to ask for the inputs? Then you could just supply both dates as parameters to a sproc.|||My front end is an Access 2003 an MDB, I need to change it to a ADP but Jet SQL and MSSQL dont exactly speak the same language|||Sure they do, I use pass-through queries all the time.

I use code to get my parameter values from the user, and then change the
QueryDef.SQL on the fly.

Start a new query, then go to Query -> Sql Specific -> Pass-Through

Then for your SQL
Execute YourSproc "paramvalue1", "paramvalue2"|||YESSSS

SELECT [IR Number], Date, Inspector, Area, Violation, [Violation Type], Loss, [Loss Type], Employee, Action, Notes, [Action Type]
FROM dbo.[Main Table]
WHERE (Date BETWEEN @.StartDate AND @.EndDate)
ORDER BY [IR Number]

I'M SO HAPPY IT WORKS FINALLY HOOOORRAAAAYYYYY

HAPPY DANCE!!!!!!!!!!!!!!!!!!!!!!

between

SQL Server2000 SP3
Field date is smalldatetime
I have 8 rows with data='06-Aug-2003'
SELECT * FROM TEST
where data between '06-Aug-2003' and '06-Aug-2003'
Results: 0 Rows
SELECT * FROM TEST
where data>='06-Aug-2003' and data<='06-Aug-2003'
Results: 0 Rows
What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')What is the time portion of the field ?
What does this give you
SELECT * FROM TEST
where data between '20030806' and '20030807'
OR
SELECT * FROM TEST
where CONVERT(char(8),data,112) = '20030806'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Dragos Hilbert" <dragos_hilbert@.yahoo.co.uk> wrote in message
news:eMh$KHAXDHA.652@.TK2MSFTNGP10.phx.gbl...
> SQL Server2000 SP3
> Field date is smalldatetime
> I have 8 rows with data='06-Aug-2003'
> SELECT * FROM TEST
> where data between '06-Aug-2003' and '06-Aug-2003'
> Results: 0 Rows
>
> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
> Results: 0 Rows
> What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')
>
>|||> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
SELECT * FROM test
WHERE data>='20030806' and data<'20030807'
(a) between can be ambiguous, I tend to avoid it for datetime queries.
(b) since datetime columns have time also, the above is converted to
midnight, so you won't likely have rows where "data" is between a day at
midnight and the same day at midnight.
(c) never use SELECT * in production code.
(d) consider changing the column name. "data" is not very descriptive at
all; I would never guess that it is a datetime column...

Better way to use BETWEEN Begin and End Dates

/*
Subject: How best to use BETWEEN Begin and End Dates to find out if an
employee
was/is member of any group for a certain date range?

You can copy/paste this whole post in SQL Query Analyzer or Management
Studio and
run it once you've made sure there is no harmful code.

I am working on an existing database where there is code that is using
BETWEEN logic and three different OR conditions to search for a user that
has worked between begin and end date parameters that you search for.
For me the three WHERE conditions with the Begin and End dates are a little
confusing so I would like to know if there's a better/simpler way to write
this.

1- I have groups table with GroupID, Name
2- I have employees table with EmployeeID, LastName, FirstName
3- I have employeegroups table where the EmployeeID has the GroupID he/she
was/is a member of and from what Begin to what End dates.
The employee can never be a member of two groups in any date interval.
The employee always was/is a member of a group from a certain to a certain
date and then the next group he/she is a member of a group begins 1 date
after the previous group membership's end date. Therefore If I worked from
2006-01-01 to 2006-01-31 and then I changed group, well in this database
the
next group dates would begin at 2006-02-01 till an Open Ended default date
of
2009-12-31.
I can also be a member of a group for 1 day: 2006-05-05 to 2006-05-05

Please continue to read below at the bottom.

*/

USE tempdb
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'EmployeeGroups' AND xtype
= 'U')
BEGIN
TRUNCATE TABLE EmployeeGroups
DROP TABLE EmployeeGroups
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Groups' AND xtype = 'U')
BEGIN
TRUNCATE TABLE Groups
DROP TABLE Groups
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Employees' AND xtype =
'U')
BEGIN
TRUNCATE TABLE Employees
DROP TABLE Employees
END
GO

CREATE TABLE dbo.Groups
(
GroupID int NOT NULL,
Name varchar(50) NOT NULL
CONSTRAINT PK_Groups PRIMARY KEY NONCLUSTERED
(
GroupID
)
)
GO

CREATE TABLE dbo.Employees
(
EmployeeID int NOT NULL,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL
CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED
(
EmployeeID
)
)
GO

CREATE TABLE dbo.EmployeeGroups
(
EmployeeID int NOT NULL,
GroupID int NOT NULL,
BeginDate datetime NOT NULL,
EndDate datetime NOT NULL,
CONSTRAINT PK_EmployeeGroups PRIMARY KEY NONCLUSTERED
(
EmployeeID,
GroupID
),
CONSTRAINT FK_EmployeeGroups_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES Employees(EmployeeID),
CONSTRAINT FK_EmployeeGroups_Groups FOREIGN KEY
(
GroupID
) REFERENCES Groups(GroupID)
)
GO

INSERT Groups (GroupID, Name)
SELECT 1, 'Group1' UNION ALL
SELECT 2, 'Group2' UNION ALL
SELECT 3, 'Group3' UNION ALL
SELECT 4, 'Group4'
GO

INSERT Employees (EmployeeID, LastName, FirstName)
SELECT 1, 'Davolio', 'Nancy' UNION ALL
SELECT 2, 'Fuller', 'Andrew' UNION ALL
SELECT 3, 'Leverling', 'Janet' UNION ALL
SELECT 4, 'Peacock', 'Margaret' UNION ALL
SELECT 5, 'Buchanan', 'Steven'
GO

INSERT EmployeeGroups (EmployeeID, GroupID, BeginDate, EndDate)
SELECT 1, 3, '1990-01-01', '2004-10-15' UNION ALL
SELECT 1, 4, '2004-10-16', '2004-10-16' UNION ALL
SELECT 1, 1, '2004-10-17', '2099-12-31' UNION ALL
SELECT 3, 2, '1999-11-15', '2002-02-22' UNION ALL
SELECT 3, 4, '2002-02-23', '2099-12-31' UNION ALL
SELECT 4, 3, '2006-05-17', '2099-12-31'
GO

--SELECT * FROM Groups
--SELECT * FROM Employees
--SELECT * FROM EmployeeGroups

DECLARE @.EmployeeID INTEGER
DECLARE @.BeginDate DATETIME
DECLARE @.EndDate DATETIME

PRINT 'First example of querying...'
SET @.EmployeeID = 1
SET @.BeginDate = 'Sep 18 2005 12:00:00:000AM'
SET @.EndDate = 'Sep 24 2006 12:00:00:000AM'

-- This is the code logic being used in the database I am looking at.
SELECT *
FROM EmployeeGroups
WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(EmployeeGroups.BeginDate <= @.BeginDate AND EmployeeGroups.EndDate

Quote:

Originally Posted by

>= @.EndDate)

OR (EmployeeGroups.BeginDate >= @.BeginDate AND
EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
@.EndDate)
)

PRINT 'Second example of querying...'
SET @.EmployeeID = 1
SET @.BeginDate = 'Sep 18 2003 12:00:00:000AM'
SET @.EndDate = 'Oct 16 2004 12:00:00:000AM'

-- This is the code logic being used in the database I am looking at.
SELECT *
FROM EmployeeGroups
WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(EmployeeGroups.BeginDate <= @.BeginDate AND EmployeeGroups.EndDate

Quote:

Originally Posted by

>= @.EndDate)

OR (EmployeeGroups.BeginDate >= @.BeginDate AND
EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
@.EndDate)
)

-- For me I would swap the parameters and the EmployeeGroups.BeginDate and
-- EmployeeGroups.EndDate like this because it's easier for me to understand
the code
-- this way.
PRINT 'Third example of querying the same parameters of the second
example...'
SELECT *
FROM EmployeeGroups
WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(@.BeginDate >= EmployeeGroups.BeginDate AND @.EndDate <=
EmployeeGroups.EndDate)
OR (@.BeginDate <= EmployeeGroups.BeginDate AND @.EndDate >=
EmployeeGroups.BeginDate)
OR (@.BeginDate <= EmployeeGroups.EndDate AND @.EndDate >=
EmployeeGroups.EndDate)
)

/*
I was doing some research on the newsgroup and I saw some similar examples
where Celko and David
Portas where using or discussing about using calendar tables:

http://groups.google.com/group/comp...f580af5913efcce
http://groups.google.com/group/comp...439bd3af4345ac1
First I am not sure if those examples can be applied to my scenario and also
I am not
sure how to use and whether or not I should be using a calendar table in
this case?

My question is there a better/simpler way to write these WHERE conditions or
the
whole statement?

I would appreciate any help on this.

Thank you

*/On Sun, 24 Sep 2006 12:48:20 -0400, serge wrote:

Quote:

Originally Posted by

>/*
>Subject: How best to use BETWEEN Begin and End Dates to find out if an
>employee
>was/is member of any group for a certain date range?

(snip)

Quote:

Originally Posted by

>-- This is the code logic being used in the database I am looking at.
>SELECT *
>FROM EmployeeGroups
>WHERE EmployeeGroups.EmployeeID = @.EmployeeID
AND (
(EmployeeGroups.BeginDate <= @.BeginDate AND EmployeeGroups.EndDate

Quote:

Originally Posted by

= @.EndDate)

OR (EmployeeGroups.BeginDate >= @.BeginDate AND
>EmployeeGroups.BeginDate <= @.EndDate)
OR (EmployeeGroups.EndDate >= @.BeginDate AND EmployeeGroups.EndDate <=
>@.EndDate)
)

(snip)

Quote:

Originally Posted by

>My question is there a better/simpler way to write these WHERE conditions or
>the
>whole statement?

Hi Serge,

First of all: thanks for providing CREATE TABLE and INSERT statements
with your question. That made it very easy to understand your question
and to test my reply before posting it.

Second: Yes, this can be made much simpler. I've struggled with the
"overlappping intervals" question myself once, and suddenly found the
best solution after drawing many sets of two intervals on an axis to
explore the different possibilities of overlap or non-overlap. It turned
out that there are many ways in which two intervals can overlap, but
only two ways in which they can not overlap - if interval A ends before
interval B starts, or if interval A starts after interval B ends. So the
intervals do NOT overlap IF A.end < B.start OR A.start B.end (note:
you must change < and to <= and >=, depending on your definition of
"overlap"). Reverse this to find that the condition for overlapping
intervals is simple IF A.end >= B.Start AND A.start <= B.end

Or, in your query:

SELECT *
FROM EmployeeGroups
WHERE EmployeeID = @.EmployeeID
AND BeginDate <= @.EndDate
AND EndDate >= @.BeginDate

--
Hugo Kornelis, SQL Server MVP|||Second: Yes, this can be made much simpler. I've struggled with the

Quote:

Originally Posted by

"overlappping intervals" question myself once, and suddenly found the
best solution after drawing many sets of two intervals on an axis to
explore the different possibilities of overlap or non-overlap.

Thanks Hugo. I actually drew all the possible intervals and I had
16 possibilities.

In any case your example is much easier to grasp. I'll work on
memorizing the logic so that I'm able to write this again without
reading the explanation.