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 PROBLEM IN DATETIME QUERY

I have some Records in my table like that
2006-06-16 14:15:01.607
2006-06-16 14:17:28.187
2006-06-15 14:17:28.000
2006-06-13 14:17:28.000
2006-06-17 14:17:28.000
2006-06-19 14:17:10.000
2006-06-17 14:17:28.000
I want to have count number of the records if they have same date. I dont
care about hour,minute and second info. I care about query result based on
daily records.
When I Wrote
DECLARE @.Date AS VARCHAR(50)
DECLARE @.Date2 AS VARCHAR(50)
SELECT @.Date='13.06.1900'
SELECT @.Date2='13.06.2010'
SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> @.Date
It returns
13.06.2006 1
15.06.2006 1
16.06.2006 2
17.06.2006 2
19.06.2006 1
it is right one..
When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2
instead of > @.Date )
DECLARE @.Date AS VARCHAR(50)
DECLARE @.Date2 AS VARCHAR(50)
SELECT @.Date='13.06.1900'
SELECT @.Date2='13.06.2010'
SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
HAVING CONVERT (VARCHAR(10), Referer_time,104 )
BETWEEN @.Date AND @.Date2
It olny returns
13.06.2006 1
What is the mistake ?
Could anyone help me ?Why are you converting in your having/where clause?
Take a look at this
create table TBL_referer (Referer_time datetime)
insert TBL_referer values('2006-06-16 14:15:01.607 ')
insert TBL_referer values('2006-06-16 14:17:28.187')
insert TBL_referer values('2006-06-15 14:17:28.000')
insert TBL_referer values('2006-06-13 14:17:28.000 ')
insert TBL_referer values('2006-06-17 14:17:28.000')
insert TBL_referer values('2006-06-19 14:17:10.000')
insert TBL_referer values('2006-06-17 14:17:28.000')
DECLARE @.Date AS datetime
DECLARE @.Date2 AS datetime
SELECT @.Date='19000613'
SELECT @.Date2='20100613'
-- >
SELECT CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
WHERE Referer_time > @.Date
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
--Between
SELECT CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
from TBL_referer
WHERE Referer_time BETWEEN @.Date AND @.Date2
Group BY
CONVERT (VARCHAR(10), Referer_time,104 )
You see no HAVING but a WHERE clause and no convert either
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Savas Ates wrote:
> I have some Records in my table like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-19 14:17:10.000
> 2006-06-17 14:17:28.000
> I want to have count number of the records if they have same date. I dont
> care about hour,minute and second info. I care about query result based on
> daily records.
> When I Wrote
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> It returns
> 13.06.2006 1
> 15.06.2006 1
> 16.06.2006 2
> 17.06.2006 2
> 19.06.2006 1
> it is right one..
> When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2
> instead of > @.Date )
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> BETWEEN @.Date AND @.Date2
> It olny returns
> 13.06.2006 1
> What is the mistake ?
> Could anyone help me ?|||Thank You For your helpinh But How come Sql server evaluates
That.
WHERE Referer_time BETWEEN @.Date AND @.Date2
I Have
SELECT @.Date='19000613'
SELECT @.Date2='20060618'
But my records are like that
2006-06-16 14:15:01.607
2006-06-16 14:17:28.187
2006-06-15 14:17:28.000
2006-06-13 14:17:28.000
2006-06-17 14:17:28.000
2006-06-13 14:17:10.000
2006-06-17 14:17:28.000
can you give me an article or explanation how it does it ?|||Here is all you need
http://www.karaszi.com/SQLServer/info_datetime.asp
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Savas Ates wrote:
> Thank You For your helpinh But How come Sql server evaluates
> That.
> WHERE Referer_time BETWEEN @.Date AND @.Date2
> I Have
> SELECT @.Date='19000613'
> SELECT @.Date2='20060618'
> But my records are like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-13 14:17:10.000
> 2006-06-17 14:17:28.000
> can you give me an article or explanation how it does it ?|||This should help you: http://www.karaszi.com/SQLServer/info_datetime.asp. Yo
u can post back if you
still have problems after reading the article.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Savas Ates" <in da club> wrote in message news:OE457ZdlGHA.4052@.TK2MSFTNGP05.phx.gbl...[co
lor=darkred]
>I have some Records in my table like that
> 2006-06-16 14:15:01.607
> 2006-06-16 14:17:28.187
> 2006-06-15 14:17:28.000
> 2006-06-13 14:17:28.000
> 2006-06-17 14:17:28.000
> 2006-06-19 14:17:10.000
> 2006-06-17 14:17:28.000
> I want to have count number of the records if they have same date. I dont
care about hour,minute
> and second info. I care about query result based on daily records.
> When I Wrote
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> It returns
> 13.06.2006 1
> 15.06.2006 1
> 16.06.2006 2
> 17.06.2006 2
> 19.06.2006 1
> it is right one..
> When I change it with that (Only Changing is BETWEEN @.Date AND @.Date2 i
nstead of > @.Date )
> DECLARE @.Date AS VARCHAR(50)
> DECLARE @.Date2 AS VARCHAR(50)
> SELECT @.Date='13.06.1900'
> SELECT @.Date2='13.06.2010'
>
> SELECT @.Date=CONVERT (VARCHAR(10),@.Date,104)
> SELECT @.Date2=CONVERT (VARCHAR(10),@.Date2,104)
>
> SELECt CONVERT (VARCHAR(10), Referer_time,104 ) ,Count(*)
> from TBL_referer
> Group BY
> CONVERT (VARCHAR(10), Referer_time,104 )
>
> HAVING CONVERT (VARCHAR(10), Referer_time,104 )
> BETWEEN @.Date AND @.Date2
> It olny returns
> 13.06.2006 1
> What is the mistake ?
> Could anyone help me ?
>
>
>[/color]

BETWEEN clause & <= operators

Hi ,
In a stored procedure when retrieving records based on a DATETIME values in WHERE clause - can we use BETWEEN clause or Col <= AND Col >= ?
Please suggest which is the optimised way.
Thanks in Advance,
Hari Haran ArulmozhiThey are both the same. The optimser comverts BETWEEN to >= and <= anyway. Depends on what you prefer to type\ read. I like BETWEEN as I don't have to check if there is a >= to correspond with any <= I find.

HTH|||optimized approach for datetime ranges involving two dates is actually to use something like this --

where datetimecol >= '2006-08-09'
and datetimecol < '2006-08-11'this returns all datetimes for the 9th and the 10th

using BETWEEN you have two choices -- code the upper value as '2006-08-10 23:59:59.999' (clumsy) or code the upper end as '2006-08-11' (and risk getting a row from the 11th at midnight)

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:
>

Better proformance using join or sub-query?

All,
I am using a trigger to keep a "transaction date" column up-to-date with
the datetime the record was lasted inserted/updated. My question is which
SQL statement would provide better performance:
Update tablename set transdate = getdate() where primarykey in (select
primarykey from inserted)
or
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Thanks,
James K.I recommend
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
> with the datetime the record was lasted inserted/updated. My question is
> which SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||I think the second one
Madhivanan|||JKL
I'd go with the first one. The probelm with the second one is that under
some conditions you can get a different ( wrong) output
David Portas has written some script/test about this
CREATE TABLE Countries
(countryname VARCHAR(20) NOT NULL PRIMARY KEY,
capitalcity VARCHAR(20));
CREATE TABLE Cities
(cityname VARCHAR(20) NOT NULL,
countryname VARCHAR(20) NOT NULL
REFERENCES Countries (countryname),
CONSTRAINT PK_Cities
PRIMARY KEY (cityname, countryname));
INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
INSERT INTO Cities VALUES ('Washington', 'USA');
INSERT INTO Cities VALUES ('London', 'UK');
INSERT INTO Cities VALUES ('Manchester', 'UK');
The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.
Try these two identical UPDATE statements with a small change to the
primary key in between.
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
cityname);
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* don't do this! */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
You get this from the first SELECT statement:
countryname capitalcity
-- --
UK London
USA Washington
and this from the second:
countryname capitalcity
-- --
UK Manchester
USA Washington
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
with
> the datetime the record was lasted inserted/updated. My question is which
> SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||Uri,
The original poster want to update the column the getDate().
I think, As long as you dont depend on the joined table for the
value to update, you are safe.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Of9GdAoGFHA.2420@.TK2MSFTNGP14.phx.gbl...
> JKL
> I'd go with the first one. The probelm with the second one is that under
> some conditions you can get a different ( wrong) output
> David Portas has written some script/test about this
> CREATE TABLE Countries
> (countryname VARCHAR(20) NOT NULL PRIMARY KEY,
> capitalcity VARCHAR(20));
> CREATE TABLE Cities
> (cityname VARCHAR(20) NOT NULL,
> countryname VARCHAR(20) NOT NULL
> REFERENCES Countries (countryname),
> CONSTRAINT PK_Cities
> PRIMARY KEY (cityname, countryname));
> INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
> INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
> INSERT INTO Cities VALUES ('Washington', 'USA');
> INSERT INTO Cities VALUES ('London', 'UK');
> INSERT INTO Cities VALUES ('Manchester', 'UK');
> The MS-syntax makes it all too easy for the developer to slip-up by
> writing ambiguous UPDATE...FROM statements where the JOIN criteria is
> not unique on the right side of the join.
> Try these two identical UPDATE statements with a small change to the
> primary key in between.
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
> ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
> cityname);
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* don't do this! */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> You get this from the first SELECT statement:
> countryname capitalcity
> -- --
> UK London
> USA Washington
> and this from the second:
> countryname capitalcity
> -- --
> UK Manchester
> USA Washington
>
>
> "-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
> news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> with
>|||On Fri, 25 Feb 2005 13:00:35 +0530, avnrao wrote:

>Umi, can you please explain the rationale behind it.
Hi Av.,
Mind if I do instead of Uri?
The ANSI syntax and the proprietary UPDATE FROM syntax behave only the
same if for each row in the updated table that satisfies the WHERE
clause, exactly one row in the other table matches the join criteria.
If it's possible that no rows are matched, the ANSI syntax will set the
column to NULL, unless excluded from the update in the WHERE clause. The
UPDATE FROM syntax will not update this row: since it doesn't satisfy
the join criteria, it won't be updated even if it does meet the
requirements of the WHERE clause.
(Note: this difference can be circumvened by using outer join instead of
inner join to make the joined update behave exactly as the ANSI version)
The biggest difference is when one row to be updated can be matched
against more than one row in the source table. This is what's happening
in David's script, as posted by Uri.
In the ANSI version, if the subquery in the SET clause returns more than
one row, you'll get an error message. That ensures that you will revisit
the query and change it so that it will always return exactly one row -
the row that you want it to return.
The UPDATE FROM syntax won't throw an error if a row in the table to be
updated matches against multiple rows from the source. Instead, SQL
Server will just pick one of these rows and use that one to determine
the new values in your table. Or rather, in case you want the full
details, it will update the same row over and over again with each
matching row - and since the result of all but the last change is
overwritten, the net effect is that only the results from the row
processed last will stick.
The above explains why the result of an UPDATE FROM where one row to be
updated matches more than one row from the source is completely
unpredictable: the result will be from the last row processed, but there
is no way to predict the order of evaluation SQL Server will choose for
your query. David's example shows how an index can change the order, but
there are other possibilities: parallellism, available memory, workload,
and the possibility to "piggy-back" another query are just some examples
of how the order of evaluation (and hence the result of the update) may
change between successive execution, even if no schema or data has been
changed!
I hope this helps.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Best way of dumping the rows of a table to another table

Hi,

I have two table as follows

Tabel Cat

{

ID

Update Datetime

}

Table Master

{

UniqueId

Cat Datetime

}

I wanted to insert millions of rows from Cat to Master table and can be achieved from the following query

Code Snippet

IINSERT INTO Master (UniqueId, Cat )
SELECT ID, Update from Cat

Question : Is there any better way do achieve the same operation with better performance mean to say that the execution should be less than the time it takes to execute the above statement

Thanks for your time

~Mohan

There are some elaborate things you might do (looping, BCP etc) but the time to develope and test those will MORE than eat up the time it takes to run a probably-quite-efficient update like you've create above.

You might experiment with disabling the indexes on the target table, and then rebuilding them after the insert is complete (if your application will tolerate the indexes being offline for a period). Indexes tend to generally slow down inserts.

Code Snippet

ALTER INDEX ALL on Master DISABLE;

IINSERT INTO Master (UniqueId, Cat )
SELECT ID, Update from Cat

ALTER INDEX ALL ON Master REBUILD;

|||

Hello Mohan,

'Insert into' statement writes to log and will take a long time to insert millions of rows. I would use the follwoing to make it quicker...

1. drop any existing second table. In your case t is 'table Master'.

2. Execute this query - select * into master from Cat

3. This will create and populate the rows into master table. Note 'master' table will have the same column names as in Cat.

4. You can rename the columns using 'sp_rename'. Look it up in BOL. Check if you can rename the columns in designer.

Regards....

|||

Hi ,

If we do not have indexes on the table then the query will be same as the one i mentioned. In that case it will be time cosuming to insert millions of rows..right?

~Mohan

|||

Hello AsianIndian,

I can't drop the table and the column name are different and i cannot change the column names too as the master tabel has different intentiion.

Can u please let me know if there is any other way of doing it

~Mohan

|||

Hello Mohan,

1. Do you have any rows in Master table? If yes, approx how many?

2. Did you try BCP OUT and BCP IN?

regards......

|||

Hello,

BCP is a command line utility to read into or form a file. I don't think so we cann't use it to dump data from one tabel to another table.

~Mohan

|||

Disabling the indexes is a very good idea. You might also see if you can partition the rows you are inserting into seperate batches of some length (based on testing)

The problem is that each insert requires a log write. So one row at a time in a normal RDBMS operation can take longer than doing multiples. However, if you try to insert too many rows at a time, you get a situation where it takes a lot more time because things cannot be committed, using more memory.

However, along the lines that the other person mentioned, I would consider writing a simple SSIS (2005 and up) or DTS (2000 and earlier) package to bulk insert the data (might be labeled fast load) along with the disabling/dropping of indexes. I have used this technique on several occasions to reduce a process of inserting rows that took over an hour to just taking a few minutes.

|||

Hi,

I am agree with rusag2 and Louis Davidson..

I also would like to suggest to use SSIS or DTS to transfer the huge number of data and run it .. and run them on the server throuch scheduled job(instead on Client) which makes it to perform much faster than runnin it in Management Studio or Query Analyzer..