query question

This is my current query that I have in an App I wrote:
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,City, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
ThanksOn Wed, 17 Nov 2004 15:58:44 -0800, johnfli wrote:
>This is my current query that I have in an App I wrote:
(snip)
>I need to modify it a little I need to have one query that will return when
>the DeliveredDate is empty
>and anohter query to return the ones that have somethign in the
>DeliveredDate field.
Hi johnfli,
Add "WHERE DeliveredDate IS NULL" or "WHERE DeliveredDate IS NOT NULL"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

query question

This is my current query that I have in an App I wrote:
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,Ci
ty, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
ThanksOn Wed, 17 Nov 2004 15:58:44 -0800, johnfli wrote:

>This is my current query that I have in an App I wrote:
(snip)
>I need to modify it a little I need to have one query that will return when
>the DeliveredDate is empty
>and anohter query to return the ones that have somethign in the
>DeliveredDate field.
Hi johnfli,
Add "WHERE DeliveredDate IS NULL" or "WHERE DeliveredDate IS NOT NULL"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

query problem

hi,

I need this as a filter criteria in my where clause for my select statement,

I need to set

Year1 = current year and

Year2 = currentyear-1

when current date's [month-day] > july30th

current year is in the form of 07 or 06 or 05

i.e

Year1 = 07

Year2 = 06

when current date's[ month-day] < july01

Year1 = current year - 1 and

Year2 = currentyear-2

Year1 = 06

Year2 = 05

I know I can use datepart, but current date from getdate() function gives

2007-10-07-hh.min.sec

and I need to compare this with

july 10th or june30th at any point of a year

also

set Year1

Year2 in the form 07 or 06 or 05

thnx

I suggest that this, and many date related queries you now have or will have in the future will be so much easier if you were to explore using a Calendar table. See this source.

Seems like you need to compare a date against Jul 01, so DATEDIFF() 'could' help.

Here is one simple use of DATEDIFF():

DECLARE
@.DateToVerify datetime,
@.FYear datetime

SELECT
@.DateToVerify = getdate(),
@.FYear = dateadd( day, 181, dateadd( year, datediff( year, 0, getdate() ), 0 ))

SELECT CASE
WHEN datediff( day, @.FYear, @.DateToVerify ) >= 0 THEN '07'
WHEN datediff( day, @.FYear, @.DateToVerify ) >= (-365) THEN '06'
WHEN datediff( day, @.FYear, @.DateToVerify ) >= (-730) THEN '05'
ELSE '04'
END

Most likely there are many ways to 'skin this cat', as the saying goes. But a Calendar table is often the most robust method of handling date span/range issues.