datetime in SQL Server 2000 SP2

Hello!
I have datetime column <CreatedDate> in table <sale>.
Here' s some sample data for CreatedDate:
2004-04-30 15:56:12.390
2004-04-30 15:59:42.000
2004-04-30 15:58:42.100
2004-04-30 16:01:06.190
2004-04-30 16:01:59.820
When running query below I'm not getting any rows back:
select name, CreatedDate
from sale
where CreatedDate between '04/01/2004' and '04/30/2004'
But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for CreaedDate '04/30/2004'.
From BOL:
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
Why sql server can't recognize '04/30/2004'?
Thanks,
Lena
Because '04/30/2004' really means '04/30/2004 00:00:00.000' and '2004-04-30
15:56:12.390' is greater than that.
Bojidar Alexandrov
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:8AAFC89E-8E21-4A93-987E-F3D142BEC4D1@.microsoft.com...
> Hello!
> I have datetime column <CreatedDate> in table <sale>.
> Here' s some sample data for CreatedDate:
> 2004-04-30 15:56:12.390
> 2004-04-30 15:59:42.000
> 2004-04-30 15:58:42.100
> 2004-04-30 16:01:06.190
> 2004-04-30 16:01:59.820
> When running query below I'm not getting any rows back:
> select name, CreatedDate
> from sale
> where CreatedDate between '04/01/2004' and '04/30/2004'
> But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for
CreaedDate '04/30/2004'.
> From BOL:
> BETWEEN returns TRUE if the value of test_expression is greater than or
equal to the value of begin_expression and less than or equal to the value
of end_expression.
>
> Why sql server can't recognize '04/30/2004'?
> Thanks,
> Lena
>
>
|||I suggest you check out my article about datetime at:
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:8AAFC89E-8E21-4A93-987E-F3D142BEC4D1@.microsoft.com...
> Hello!
> I have datetime column <CreatedDate> in table <sale>.
> Here' s some sample data for CreatedDate:
> 2004-04-30 15:56:12.390
> 2004-04-30 15:59:42.000
> 2004-04-30 15:58:42.100
> 2004-04-30 16:01:06.190
> 2004-04-30 16:01:59.820
> When running query below I'm not getting any rows back:
> select name, CreatedDate
> from sale
> where CreatedDate between '04/01/2004' and '04/30/2004'
> But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for CreaedDate '04/30/2004'.
> From BOL:
> BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of
begin_expression and less than or equal to the value of end_expression.
>
> Why sql server can't recognize '04/30/2004'?
> Thanks,
> Lena
>
>
|||Is there a way to use some date function in where clause so sql server will return all rows with CreatedDate for 04/30/2004 and will ignore time stamp?
Thanks
|||Lena,
This issue(plus many others) are discussed in the link that Tibor posted.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:BC32384D-6877-4DCF-9861-712082375A2D@.microsoft.com...
> Is there a way to use some date function in where clause so sql server
will return all rows with CreatedDate for 04/30/2004 and will ignore time
stamp?
> Thanks