Link to home
Start Free TrialLog in
Avatar of barkome
barkomeFlag for Canada

asked on

SQL Date Retrival

Hello

I have a table that has two different types of dates Calendar date and business date.
The calendar date is based on the normal calendar date, while the business date is based on a defined business rule (a 24 hr cycle is between the hours of 9am-9am)

So when I
select * from table A where B_StartDate is between '2016-06-01' and '2016-06-15'  

Open in new window

 I get 4 records returned, but when I
select * from table A where C_StartDateTime is between '2016-06-01' and '2016-06-15'

Open in new window

 I get 5 records returned.

My question is, how can I select * from table A with the B_Startdate and get the 5 records expected. Knowing that the hour business rule has to be considered.

I can only retrieve records based on B_Startdate only. Sample data attached.

User generated image
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
(may be redundant with Kevin's post above)

>select * from table A where C_StartDateTime is between '2016-06-01' and '2016-06-15'
For starters, using BETWEEN includes both values, but when dates are passed without a time component then the time will be as of the beginning midnight that day '2016-06-15 12:00:00 AM', which means your return set will NOT include values with '2016-06-15' but with a time component after beginning midnight.  Check out BEWARE of BETWEEN for an illustration.

So, give this a whirl..
select * 
from table A 
where C_StartDateTime is >= '20160601' and C_StartDateTime < '20160616'

Open in new window

Also .. confirm for us that these columns are dates and not varchar's, as varchar's will process greater then/less than logic alphabetically and not date-numerically..
Great extra detail, Jim.  If these are indeed dates, I would go with split criteria as I would avoid BETWEEN as Jim said.  In addition, I would think you would want to stop before 9am, which is why I switch the code in the first place as I think this is more appropriate once you add time.

B_StartDate < DATEADD(HH, 9, @end_dt)

BETWEEN is equivalent to >= AND <= but think in your scenario you want everything after and including 9am of the start date but less than and not including 9am of the end date.
Avatar of barkome

ASKER

The record with a businessmonth = 5 didnt appear.

Dates are varchar converted date.

Used this syntax to test:

and convert(date,B_StartDate ) >= DATEADD(HH, -15, '2016-06-01')
AND convert(date,B_StartDate ) <= DATEADD(HH, 9,'2016-06-15')
I see the problem now.  The start date is at midnight of the 31st, so it should not count if your business day starts at 9am.  Therefore, you either need to use the other column OR use the end date as your filter.
With below code you will never get the fifth row.

select * from table A where B_StartDate is between '2016-06-01' and '2016-06-15'

Open in new window


5/31/16 12:00 AM is 31st July morning 12. So ideally it is not 1st june, so it should not be included. if we are checking for june 1st to june 15th.

Please use some other column to get the 5th row. You can use C_StartDateTime like you are using.

Hope it helps !!