To get date part only in SQL out of datetime field stored as getdate()

[MANU]

I am trying to run the query :

SELECT Payment_Date
FROM dbo.tbl_PaymentDt
WHERE Payment_Date BETWEEN '08/06/2002' AND 
'08/07/2002'

In the table, Payment_Date is stored as default value getdate(). The above query results in the records for '08/06/2002' only, even if the records are present for '08/07/2002'. This problem is probably due to the format in which date is stored. Here date is stored along with time.

Pl tell me the work around so that when dates are compared, time is ignored.

Pl tell me a function available in SQl which only give date minus time from a field stored as GetDate().

Thanks. Waiting for ur reply.

 

[Sqlsa]

Hi Manu,
Try DatePart function..

example:

select Payment_Date from tbl_Paymentdate
where datepart(yyyy,payment_date)=2002
and datepart(mm,payment_date)=08
and datepart(dd,paymentdate)<=6
and datepart(dd,paymentdate)>=7

The ablove select will populate all records for which Payment_date is in between '08/06/2002' and '08/07/2002'...

Refer Date and Time Functions in BOL.
-------------------------------------

Hope it helps.

Sreenivas
avnsr@hotmail.com
-----------------

 

[phillyon]

The between function will only return you dates between the two you have specified. In this case your dates are 08 06 2002 at 00:00:00:000 time and 08 07 2002 at 00:00:00:000 time. This means you will only get dates between 08 06 2002 at 00:00:00:001 (the first one thousandth of a second of the day) and 08 06 2002 at 23:59:59.999 (last one thousandth of a second of the day).

If you want both days data you can do:

where yourfielddate between 08 06 2002 and 08 08 2002 or

where datediff(day, '08 06 2002', yourfielddate) <= 1

Check out the help on the datediff function for more.

hope this helps,

Cheers,

Phil.