DateTime and SQL

[imfarhan]

Hi All,

I have used the following condition in my SQL
WHERE DischargeDate >= (#10/01/2009#) and DischargeDate <= (#12/31/2009#)

But some patients who Discharged on 31/12/2009 are not coming I think coz of the timing the field keeps the time value too.

I have also tried the following condition instead of above but still not success.
DischDate < DateAdd("d",1,#12/31/2009#)

I have also used between instead off <= and >= but still not bring the rows which patients discharge on 31/12/2009

I also tried the folllowing condition in my SQL
Between dischargedate format(01/09/2009,"dd/mm/yyyy") and format(31/12/2010,"dd/mm/yyyy") & ?????

I don't konw how can I used the time constant value i.e. 23:59:59

I hope it does make sense to you.

Thanks in advance
Regards
Farhan

 

[Joe4]

When there is no time component, it is assumed to be 0:00 (or 12:00 AM). So why not just change your condition to:

WHERE DischargeDate >= (#10/01/2009#) and DischargeDate < (#1/1/2010#)

 

[imfarhan]

Hi ,

The field keeps the time value in all rows along with date as given below:-


The following patient not coming into my query don't know why?

Name DischDate
Mr Patel 31/12/2009 16:09:00


The orginal query as follows:-

WHERE ((smsmir_admission.DischDate)>=DateAdd("d",-30,#10/1/2009#) And (smsmir_admission.DischDate)<DateAdd("d",1,#12/31/2009#))
And ((smsmir_admission.Specialty) Not In ('501','510','560','610'))
And ((nhhtlod_IPActivity.FirstCE)=1) And ((smsmir_admission.Hospital)="NHH" Or (smsmir_admission.Hospital)="ERDS" Or (smsmir_admission.Hospital)="DTC") Or (((smsmir_admission.DischDate) Is Null) And ((smsmir_admission.Specialty) Not In ('501','510','560','610')) And ((nhhtlod_IPActivity.FirstCE)=1) And ((smsmir_admission.Hospital)="NHH" Or (smsmir_admission.Hospital)="ERDS" Or (smsmir_admission.Hospital)="DTC"))
ORDER BY Val(smsmir_admission.InternalNo), smsmir_admission.AdmitDate;

 

[Joe4]

Without knowing the value of all your other fields used in your criteria, it is impossible for me to say why that record isn't being returned.

Does my date logic I posted in my last post make sense to you? "12/31/2009 16:09:00" is less than "1/1/2010" , so that should pick it up (whereas your original one was cutting it off at "12/31/2009 0:00:00").

One thing I noticed is that you appear to be using different dates format, i.e. in your data you are showing "dd/mm/yyyy" while in your criteria, you have "mm/dd/yyyy". I am not sure if that may be part of the problem.

 

[imfarhan]

Thanks Joe.
I'm running the query ..its too slow...
I will come back to you if its runs ok or not.

 

[imfarhan]

Wow great this record(Patel) I can see now ... I have to do thorough testing.
Once again thanks for your help. If any problem I will come back to you.

Regards
Farhan