Home All Groups Group Topic Archive Search About

BETWEEN PROBLEM IN DATETIME QUERY

microsoft.public.sqlserver.programming
Author
22 Jun 2006 8:33 AM
Savas Ates
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 ?

Author
22 Jun 2006 8:51 AM
SQL Menace
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:
Show quote
> 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 ?
Author
22 Jun 2006 9:11 AM
Savas Ates
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 ?
Author
22 Jun 2006 9:21 AM
SQL Menace
Here is all you need
http://www.karaszi.com/SQLServer/info_datetime.asp

Denis the SQL Menace
http://sqlservercode.blogspot.com/


Savas Ates wrote:
Show quote
> 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 ?
Author
22 Jun 2006 9:28 AM
Tibor Karaszi
This should help you: http://www.karaszi.com/SQLServer/info_datetime.asp. You 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/


Show quote
"Savas Ates" <in da club> wrote in message news:OE457ZdlGHA.4052@TK2MSFTNGP05.phx.gbl...
>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 ?
>
>
>
>
>
>

AddThis Social Bookmark Button

Post Other interesting topics
Help with varchar to date
schedule dts at a remote server
can a SELECT return this result ?!
can a SELECT return this result ?!
Issue with T-SQL and SQL 7 to SQL2000 conversion