datetime datatype question 
Author Message
 datetime datatype question
In the where clause,
I put the following code:-

Select *
From TableA
Where createdate <= '07/20/2003' and createdate >= '07/19/2003'

The result only give me July 19, 2003 (example: 2003-07-19 12:34:53.000).  I
checked the data and I know there is July 20, 2003 exist.

How come  createdate <= '07/20/2003'   didn't work? It seems like less than
work but = not work on <=

Thanks.

Karen



Fri, 06 Jan 2006 20:24:38 GMT
 datetime datatype question

The date '07/20/2003' implies a time of 00:00:00 or midnight at the beginning of that date.  Thus, any items that occurred after midnight are not included in your result set.  Try:

Select *
From TableA
Where createdate < '07/21/2003' and createdate >= '07/19/2003'

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

In the where clause,
I put the following code:-

Select *
From TableA
Where createdate <= '07/20/2003' and createdate >= '07/19/2003'

The result only give me July 19, 2003 (example: 2003-07-19 12:34:53.000).  I
checked the data and I know there is July 20, 2003 exist.

How come  createdate <= '07/20/2003'   didn't work? It seems like less than
work but = not work on <=

Thanks.

Karen



Fri, 06 Jan 2006 20:32:59 GMT
 datetime datatype question
Thanks a lot ALL. I understand now.

Karen :)


Quote:
> In the where clause,
> I put the following code:-

> Select *
> From TableA
> Where createdate <= '07/20/2003' and createdate >= '07/19/2003'

> The result only give me July 19, 2003 (example: 2003-07-19 12:34:53.000).
I
> checked the data and I know there is July 20, 2003 exist.

> How come  createdate <= '07/20/2003'   didn't work? It seems like less
than
> work but = not work on <=

> Thanks.

> Karen



Fri, 06 Jan 2006 20:51:39 GMT
 datetime datatype question
First, its a bad idea to use region-specific date formatting. Always use
"20030720" as the format to guarantee both you and SQL server know what you
are talking about.

Second, Dates in SQL are Date AND Time values. By not supplying the time
portion, SQL is assuming you mean midnight, so you are saying "where
createdate < '20030720'", which works fine, and also "where createdate <=
'20030720 00:00:00'" which will exclude any date of 20030720 and some time >
midnight.

Try specifying '20030720 23:59:59', or alternatively, add 1 day to your date
parameter and use < operator only.

Stu


Quote:
> In the where clause,
> I put the following code:-

> Select *
> From TableA
> Where createdate <= '07/20/2003' and createdate >= '07/19/2003'

> The result only give me July 19, 2003 (example: 2003-07-19 12:34:53.000).
I
> checked the data and I know there is July 20, 2003 exist.

> How come  createdate <= '07/20/2003'   didn't work? It seems like less
than
> work but = not work on <=

> Thanks.

> Karen



Fri, 06 Jan 2006 20:43:29 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. datetime datatype questions

2. Transformation of an int datatype to datetime datatype

3. Converting datatype varchar to datatype datetime in SQL trigger

4. dateTime datatype - Two quick questions

5. bcp question about the datatype--datetime

6. wants to include seconds in datetime datatype

7. DateTime datatype in SQL Server 7 ???

8. Auto update datetime datatype

9. setting datetime datatype column back to NULL

10. Datetime datatype

11. Changing varchar to datetime datatype in SQL Server 7.0 Trigger

12. insert time only into column define as datetime datatype


 
Powered by phpBB® Forum Software