• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 703
  • Last Modified:

Compare datetime columns

Hello,

Have three datetime columns in the following query that I want to compare.  Estimated_arrival_date and receipt_date contain only a date value, delivery date contains both date and time values.   I can compare estimated_arrival_date and receipt_date fine because the times show as all zeros, but how can I compare the delivery_date as it has both date and time?

select m.po_number,estimated_arrival_date, pod.delivery_date, r.receipt_date
from t_po_master m
LEFT OUTER JOIN t_po_detail pod
ON m.po_number = pod.po_number
LEFT OUTER JOIN t_receipt r
ON m.po_number = r.po_number
where estimated_arrival_date <> delivery_date
0
ckangas7
Asked:
ckangas7
1 Solution
 
Patrick MatthewsCommented:
where estimated_arrival_date <> CONVERT(datetime, CONVERT(varchar, delivery_date, 101))
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:

select m.po_number,estimated_arrival_date, pod.delivery_date, r.receipt_date
from t_po_master m
LEFT OUTER JOIN t_po_detail pod
ON m.po_number = pod.po_number
LEFT OUTER JOIN t_receipt r
ON m.po_number = r.po_number
where estimated_arrival_date <> convert(datetime, convert(varchar(10), delivery_date, 120), 120)
0
 
twintaiCommented:
you basically need to get rid of the time portion of the delivery_date. I had this problem before...here is the easiers solution...

cast(cast(delivery_date as char(12)) as smalldatetime)

the inner cast will conver the datetime field to text.
Ex 2007-10-12 15:53:11.270 = Oct 12 2007
notice it removed the time. so now you can convert it back to smalldatetime or datetime if you want..

2007-10-12 00:00:00.000
now you can compare all values as dates
0
 
ckangas7Author Commented:
Thanks all.  Looks like you are all basically doing the same thing, just in slightly different ways.  Points go to twintai as his little explanation helped me to understand what the qeury is actually doing.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now