We help IT Professionals succeed at work.

sql dates

aneilg
aneilg asked
on
i have the following query and i want to get the results between 15th and 30th aug.
do i nee to do anything special with the minutes.

AND hl.HISTORY_DATE BETWEEN 'yyyy-dd-mm 00:00:00.000' AND 'yyyy-dd-mm 00:00:00.000'
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012

Commented:
depends,  does history_date hav   hours/minutes/seconds?  If so, do you want them included?

between  is the same thing as   >=   and <=

So, if history_date =   2011-12-07 07:54:23   it will be EXCLUDED from a range  that goes up to  2011-12-07 00:00:00

if you want it to be INCLUDED,  then either bump up to the next day (which will include 00:00:00 of that day) or use 23:59:59

Author

Commented:
thanks if i try AND hl.HISTORY_DATE BETWEEN '2011-15-08 00:00:00.000' AND '2011-31-08 00:00:00.000'

i get The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Most Valuable Expert 2011
Top Expert 2012
Commented:
sorry, I was assuming that was pseudo-code.


If history_date is a TIMESTAMP type,  then compare it to two timestamps, not to strings.

AND hl.HISTORY_DATE BETWEEN TO_TIMESTAMP('2011-15-08 00:00:00.000','yyyy-dd-mm hh24:mi:ss.ff') AND to_timestamp('2011-31-08 00:00:00.000','yyyy-dd-mm hh24:mi:ss.ff')
NorieAnalyst Assistant

Commented:
Have you tried not including the time part?
Most Valuable Expert 2011
Top Expert 2012

Commented:
if history_date is a DATE type you can do the same thing,  but the .000  part is irrelevant.

if you wanted,  you could just use   '2011-15-08'  and '2011-31-08'   with corresponding yyyy-dd-mm in the conversion mask

Author

Commented:
i think i had my dd and mm mixed up.
Most Valuable Expert 2011
Top Expert 2012

Commented:
do note,  as mentioned in the first post,  you date range is NOT going to include any data from August 31.  Unless your history_date values are all truncated to midnight (00:00:00)
try just doing
AND hl.HISTORY_DATE BETWEEN <= 'yyyy-dd-mm' AND hl.HISTORY_DATE BETWEEN >= 'yyyy-dd-mm'
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> i think i had my dd and mm mixed up

doesn't matter what order they are in, just make sure you specify the same order in your conversion mask
Most Valuable Expert 2011
Top Expert 2012

Commented:
adilet_nasirdi…


that's not legal syntax

Author

Commented:
the results are not what i expected. how do i do something like.

AND hl.HISTORY_DATE BETWEEN '2011-15-08' 'yyyy-dd-mm'AND '2011-31-08'
basically putting the date format in the query.
Most Valuable Expert 2011
Top Expert 2012

Commented:
already shown above...


http:#37249330

use to_timestamp('2011-15-08', 'yyyy-dd-mm')  or to_date('2011-15-08', 'yyyy-dd-mm')
sorry
forgotten to delete "BETWEEN"
AND hl.HISTORY_DATE >= '2011-08-15' AND hl.HISTORY_DATE  < '2011-08-31'
to get all from 15 to 30(including)

Author

Commented:
AND hl.HISTORY_DATE BETWEEN ('2011-08-15','yyyy-mm-dd') AND ('2011-08-31','yyyy-mm-dd')      
i get an error Incorrect syntax near ','.

Commented:
Hi

1)  SELECT *
FROM tablename
WHERE
        CONVERT(VARCHAR(max), [DateTime], 111) BETWEEN '2008/08/15' AND '2008/08/30'

2) Select * from tablename WHERE

 ModifiedDate

BETWEEN

 CONVERT(DateTime, '8/15/2008 12:00:00AM',131) AND CONVERT(DateTime,'8/30/2008 11:59:59PM', 131)
Most Valuable Expert 2011
Top Expert 2012

Commented:
you haven't included the "TO_DATE" or "TO_TIMESTAMP" functions
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
handle date/time correctly:
http://www.experts-exchange.com/A_1499.html

Author

Commented:
thanks