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]