sql datetime to date help

I am creating a report that I want to be able to show late shipments.  All of the fields I am working with are datetime fields.  I want to be able to highlight, change the font color, or add a field that will show the user which items were late.  So if my ShipDate was greater than my PromiseDate I want it flagged some way.  Because these are currently datetime fields, I may get some showing flagged as being late when the shipdate was the same date as the promisedate because of the time field.  

I will then want to sum these fields up some how to come up with an on time percentage.

What is the easiest way for me to do this?  Is it easier to enter this in the sql query or do it in an expression under the field properties?

I'm using Visual Studio 2012
Customer-OTD-by-Taker.rdl
LVL 4
Sue TaylorProject ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dustin SaundersDirector of OperationsCommented:
You can cast your datetime as just a date for that purpose:
SELECT CAST(GETDATE() AS date)

Open in new window


Result:
2018-01-04

Open in new window


Then you can see if the item is late or not.  (I don't have a table set up but heres an example, just select from your table)
SELECT ShipDate, PromiseDate, CASE WHEN ShipDate > PromiseDate THEN 1 ELSE 0 END AS "Late"
FROM	(
	SELECT CAST(GETDATE() AS date) AS "ShipDate", CAST(GETDATE() AS date) AS "PromiseDate"
		) AS a

Open in new window

0
Sue TaylorProject ManagerAuthor Commented:
I was not able to get this to work after trying several attempts.  I now have the date thing working, I think.  But I still cannot figure out how to count the ones that are late so that I can ultimately report the on time delivery.  Obviously I'm new to sql.  
SELECT     p21_view_oe_line.order_no, p21_view_oe_line.line_no, CONVERT(varchar, p21_view_oe_line.required_date, 1) AS date_required, 
                      p21_view_invoice_line.company_id, CONVERT(varchar, p21_view_invoice_line.date_created, 1) AS date_invoiced, p21_view_invoice_line.item_id, 
                      p21_view_invoice_line.extended_price, p21_view_oe_line.cancel_flag, p21_view_customer.customer_name, CONVERT(varchar, p21_view_invoice_hdr.ship_date, 1) 
                      AS shipped, CONVERT(varchar, p21_view_oe_line_promise_date.promise_date, 1) AS promise_date, p21_view_oe_hdr.taker, p21_view_oe_line.extended_desc, 
                      p21_view_customer.customer_id AS customer, p21_view_invoice_hdr.customer_id, DATEDIFF(d, p21_view_oe_hdr.promise_date, p21_view_invoice_hdr.ship_date) 
                      AS Expr1
FROM         p21_view_invoice_line AS p21_view_invoice_line INNER JOIN
                      p21_view_invoice_hdr AS p21_view_invoice_hdr ON p21_view_invoice_line.invoice_no = p21_view_invoice_hdr.invoice_no AND 
                      p21_view_invoice_line.order_no = p21_view_invoice_hdr.order_no RIGHT OUTER JOIN
                      p21_view_oe_line AS p21_view_oe_line ON p21_view_invoice_line.order_no = p21_view_oe_line.order_no AND 
                      p21_view_invoice_line.oe_line_number = p21_view_oe_line.line_no INNER JOIN
                      p21_view_customer AS p21_view_customer ON p21_view_invoice_hdr.customer_id = p21_view_customer.customer_id_string INNER JOIN
                      p21_view_oe_hdr AS p21_view_oe_hdr ON p21_view_invoice_line.order_no = p21_view_oe_hdr.order_no AND 
                      p21_view_invoice_line.company_id = p21_view_oe_hdr.company_id INNER JOIN
                      p21_view_oe_line_promise_date AS p21_view_oe_line_promise_date ON p21_view_oe_line.oe_line_uid = p21_view_oe_line_promise_date.oe_line_uid
WHERE     (p21_view_oe_line.cancel_flag <> 'Y') AND (NOT (p21_view_invoice_hdr.customer_id = '1200107' OR
                      p21_view_invoice_hdr.customer_id = '1200108' OR
                      p21_view_invoice_hdr.customer_id = '1200110' OR
                      p21_view_invoice_hdr.customer_id = '1200111' OR
                      p21_view_invoice_hdr.customer_id = '1200134' OR
                      p21_view_invoice_hdr.customer_id = '1200150' OR
                      p21_view_invoice_hdr.customer_id = '1200234' OR
                      p21_view_invoice_hdr.customer_id = '1200265' OR
                      p21_view_invoice_hdr.customer_id = '1200476' OR
                      p21_view_invoice_hdr.customer_id = '1200477' OR
                      p21_view_invoice_hdr.customer_id = '1200744' OR
                      p21_view_invoice_hdr.customer_id = '1200747' OR
                      p21_view_invoice_hdr.customer_id = '1200858' OR
                      p21_view_invoice_hdr.customer_id = '1200884' OR
                      p21_view_invoice_hdr.customer_id = '1203965' OR
                      p21_view_invoice_hdr.customer_id = '1206686' OR
                      p21_view_invoice_hdr.customer_id = '1209159' OR
                      p21_view_invoice_hdr.customer_id = '1214330' OR
                      p21_view_invoice_hdr.customer_id = '1303966')) AND (p21_view_invoice_line.date_created < CONVERT(DATETIME, '2018-01-01 00:00:00', 101)) AND 
                      (p21_view_invoice_line.company_id = '1') AND (p21_view_oe_hdr.taker IN (@taker)) AND (NOT (p21_view_customer.customer_id = '1200107' OR
                      p21_view_customer.customer_id = '1200108' OR
                      p21_view_customer.customer_id = '1200110' OR
                      p21_view_customer.customer_id = '1200111' OR
                      p21_view_customer.customer_id = '1200134' OR
                      p21_view_customer.customer_id = '1200150' OR
                      p21_view_customer.customer_id = '1200234' OR
                      p21_view_customer.customer_id = '1200265' OR
                      p21_view_customer.customer_id = '1200476' OR
                      p21_view_customer.customer_id = '1200477' OR
                      p21_view_customer.customer_id = '1200744' OR
                      p21_view_customer.customer_id = '1200747' OR
                      p21_view_customer.customer_id = '1200858' OR
                      p21_view_customer.customer_id = '1200884' OR
                      p21_view_customer.customer_id = '1203965' OR
                      p21_view_customer.customer_id = '1206686' OR
                      p21_view_customer.customer_id = '1209159' OR
                      p21_view_customer.customer_id = '1214330' OR
                      p21_view_customer.customer_id = '1303966')) AND (CONVERT(varchar, p21_view_invoice_line.date_created, 1) BETWEEN @StartDate AND @EndDate)
ORDER BY p21_view_oe_hdr.taker, p21_view_invoice_hdr.customer_id, p21_view_oe_line.order_no, p21_view_oe_line.line_no

Open in new window

0
Vadim RappCommented:
Add another field to your query:

Select ..., case when shipdate> promisedate then 0 else 1 end as OnTime,....

Then you can specify some element of formatting as an expression based on the value of the field OnTime , for example you can specify the color as

Iif(OnTime=1, green,red)

with the right syntax. To calculate total percentage, divide sum(OnTime) by total count.
1
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ZberteocCommented:
In the WHERE clause NEVER convert a datetime column to varchar! This will render the INDEX useless, if there is one and it should be, and make the query performing badly! What you should do is this:

AND (p21_view_invoice_line.date_created BETWEEN @StartDate AND @EndDate)

Make sure that when you assign the value to @EndDate it has the time part attcached to it: 'YYYY-MM-DD 23:59:59.997', which will ensure that that particular date is included in your results regardless of the time part. For the @StartDate you can ignore the time because it will be defaulted to the start of the day time, which is 00:00:00.

Example for the month of December in both cases when the variables are of varchar or datetime type:

declare
     @StartDate varchar(30),
     @EndDate varchar(30)

or

declare
     @StartDate datetime,
     @EndDate datetime


You will set them like this:

select
     @StartDate='2017-12-01',
     @EndDate= '2017-12-31 23:59:59.997'

In order to determine if the delivery is after the promise date you can use a computed column in the select lis:

..., case when p21_view_oe_line.required_date<p21_view_oe_line.ship_date then 'Red' else 'Blue' end as Status,

or, if you want to select those rows you include it in the where clause:

WHERE
...
and (p21_view_oe_line.required_date<p21_view_oe_line.ship_date)
0
Sue TaylorProject ManagerAuthor Commented:
I definitely see a difference in the performance when I removed the CONVERT in the WHERE clause.  Thank you for that information.  

I'm still struggling however.  I ran the report without any conversion and the Promise_Date (StartDate) is already displaying everything at midnight.  The Ship_Date(EndDate) has the date and times.  So if the Promise Date was 12/21/2017 00:00:00 and the Ship Date was 12/21/2017 2:13:04 is will show up late.  

You said to declare something.  Where do I put this?
0
PatHartmanCommented:
If time is not relevant to the process, you should NOT be logging time.  Just save the the date rather than date + time.

If you determine that you really don't need time, you should probably fix the data and change any Now()'s to Date()'s and be done with it.  Otherwise, you'll be constantly fighting the time every time you want to do something.
0
ZberteocCommented:
In that case you will have to cast your ship date to date:

..., case when p21_view_oe_line.required_date<cast(p21_view_oe_line.ship_date as date) then 'Red' else 'Blue' end as Status,

or in the where clausue:

WHERE
...
and (p21_view_oe_line.required_date<cast(p21_view_oe_line.ship_date as date))

this where clasue will filter only the invoices that are late, which means the ship date is earlier than the promise date. Your example will not be a late shipping anymore.
0
PatHartmanCommented:
When you are working with large amounts of data, you might want to index the date.  That will not help you if the date contains a time that is irrelevant and using  Cast() to constantly change the format of the date will prevent the query engine from using the index.  Always fix the data rather than making allowances to work around it in perpetuity.
0
Mark WillsTopic AdvisorCommented:
You already have the information...

You are calculating : DATEDIFF(d, p21_view_oe_hdr.promise_date, p21_view_invoice_hdr.ship_date) as Expr1

So, if Promise Date was 12/21/2017 00:00:00 and the Ship Date was 12/21/2017 2:13:04  will show as 0

Try it : SELECT datediff(d,'20171221 00:00:00','20171221 02:13:04)

The datediff is telling the calc to check difference in 'd'ays  -  so use Expr1 - any number > 0 is late, =0  as promised, <0 is early

In terms of 'WHERE", the CONVERT(DATETIME, '2018-01-01 00:00:00', 101)) doesnt matter that will get calculated once.

But the AND (CONVERT(varchar, p21_view_invoice_line.date_created, 1) BETWEEN @StartDate AND @EndDate) will get recalculated every time.

You have declared, or provide as paramters the @startdate and the @endDate. What are they ? If already DateTime, then dont convert. Would also avoid between....

Style code 1 is MM/dd/yy, style code 101 is format MM/dd/yyyy and if the parameters are already in that format, they need to be datetime, If strings, you will have problems.

So better to say "AND (p21_view_invoice_line.date_created >= @startdate AND p21_view_invoice_line.date_created < @enddate + 1) "

Or  "AND (p21_view_invoice_line.date_created >= @startdate AND p21_view_invoice_line.date_created < dateadd(d,1,datediff(d,0,@enddate)))"

but need to know more about your @startdate and @enddate
0
Sue TaylorProject ManagerAuthor Commented:
I have entered:    CAST(@StartDate AS Date)  
                               CAST(@EndDate AS Date)

I don't have datediff anymore.  And now I am working on figuring out how to put that back in.
0
Mark WillsTopic AdvisorCommented:
You dont have datediff ? Pity because Expr1 is what you needed.

To take that a step further, you could have expanded that to give you:

CASE WHEN DATEDIFF(d, p21_view_oe_hdr.promise_date, p21_view_invoice_hdr.ship_date) < 1 THEN 0 ELSE 1 END  AS LATE_DELIVERY

Then in your report you can check that field for setting colour on your delivery date :

 IIF(Fields!LATE_DELIVERY  > 0, "Red", "Black")

And in your report, you can then SUM your LATE_DELIVERY to give you the counts you are after.

Back to the query, in your WHERE clause, use:

AND (p21_view_invoice_line.date_created >= @startdate AND p21_view_invoice_line.date_created < dateadd(d,1,@enddate))


Not sure what you mean by '...how to put  that back in'

It would mean editing your stored procedure - assuming you changed it to ' removed the CONVERT in the WHERE clause'

Or is there something else ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sue TaylorProject ManagerAuthor Commented:
I had problems with Visual Studio.  It crashed a couple of times then I think that the file got messed up some how.  I'm back on track, I think.  Thanks for the info.   Let me try it because I have datediff back in.
0
Sue TaylorProject ManagerAuthor Commented:
OK, Mark.  I think I finally have the SUM of the Lates!!!   Now one last thing if you don't mind.....

Do I just change the expression on the SUM to do some math to get the percentage of lates?
0
Sue TaylorProject ManagerAuthor Commented:
Thank you for your help, Mark.
0
Mark WillsTopic AdvisorCommented:
>> Do I just change the expression on the SUM to do some math to get the percentage of lates?

You could, but I normally keep my counters/aggregates separate (hidden) and then have  what ever calculations visible. That way, if needing to debug the report I can simply make the components of the calc visible....  Got into trouble when first playing with SSRS when my calcs werent giving me the results I was expecting, (forgetting to include 'scope') and havent changed my approach since. Purists would probably think I am crazy...

So, will leave that to you :)

Cheers,
Mark Wills
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.