Solved

TSQL: create aging report by 30, 60, 90 days

Posted on 2010-08-25
18
2,068 Views
Last Modified: 2012-05-10
Hello All

THanks for reading my question.  I want to create an aging report by 30, 60, 90 days (these #'s will be in dropdown box)

This is my code so far
SELECT sched_id, sched_date, pt_id
FROM     Schedule
WHERE  (sched_paid = @status) AND (sched_date < @today + @aging)
ORDER BY sched_date

@ aging is suppose to link to a dropdown box
But i keep getting this error message
"tsql string was not recognized as a valid datetime"

any ideas on how I can do this

Thanks in advance
0
Comment
Question by:ptemo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
18 Comments
 
LVL 3

Expert Comment

by:omnimaven
ID: 33528053
instead of using @today + @aging, use something like

DATEADD(day, @aging, @today)

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33528120
I agree. please award points above, but the explanation is the concatenation operator will take precedence over the comparison.

http://www.e-e.com/A_3533-Effective-SQL-Understanding-Order-of-Operations.html

Therefore, you are ending up with something like '2008-08-2630' or '08/26/201030' which you can see isn't a valid datetime.

DateAdd should do the trick OR convert @today to datetime then +@aging will work.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33528168
Also, maybe I am reading this wrong, but normally aging is:

due_date > {30 days ago} == current
due_date <= {30 days ago} and > {60 days ago} == 30
due_date <= {60 days ago} and > {90 days ago} == 60
due_date <= {90 days ago} and > {120 days ago} == 90
due_date <= {120 days ago} == way past due *laughing*

If so then:
(sched_date < convert(datetime, @today) + @aging)

Would not yield the correct results as that will get you all records that are scheduled for a date older than 30 days from now. Therefore, you may want to switch that to -@aging and add a second check:

(sched_date <= convert(datetime, @today) - @aging
and sched_date > convert(datetime, @today) - (@aging+30))
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:ptemo
ID: 33528184
Thank you guys so much for your help

I tried this

WHERE  (sched_paid = @status) AND (sched_status = 'Yes') AND (sched_date < DATEADD(day, @aging, @today)

Sorry I'm new to TSQL, but i'm still getting an error message.  What do you think can be the problem?  Thanks again
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33528200
You left out a bracket )

i.e.,

WHERE  (sched_paid = @status) AND (sched_status = 'Yes') AND (sched_date < DATEADD(day, @aging, @today)

should be:

WHERE  (sched_paid = @status) AND (sched_status = 'Yes') AND (sched_date < DATEADD(day, @aging, @today))

Note: sched_paid is being filtered on @status and sched_status on literal 'Yes'. Just going by the name, did you possibly mean for those to be reversed (i.e., sched_status = @status).
0
 

Author Comment

by:ptemo
ID: 33528222
mwvisa1

Thanks for your help so far.  I tried your suggestion, but i now get a error message that says "Failed to conver parameter value from a decimal to datetime"

Should I convert it first

thanks
PS does it matter if a literal or variabl goes first if you use an "And"
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33528271
What are you passing as the value of @today ?

Re: PS does it matter if a literal or variabl goes first if you use an "And"
I was asking if the below is really what you meant to write:
(sched_paid = @status) AND (sched_status = 'Yes')

Or if you might have wanted this:
(sched_status = @status) AND (sched_paid = 'Yes')

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33528278
If your today value is something like 20100826 then you may have to do this:

convert(datetime, convert(char(8), @today), 112)
0
 

Author Comment

by:ptemo
ID: 33528354
today value is today's date from a label box

So this is what I got so far.
WHERE  (sched_paid = @status) AND (sched_status = 'Yes') AND (sched_date < DATEADD(day, @aging, CONVERT(datetime, CONVERT(char(8), @today), 112)))

does that appear right?

thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33528442
Yes, it appears right if all assumptions are correct. Did it work without error this time?
0
 

Author Comment

by:ptemo
ID: 33530073
Hello again,

It actually didn't work.  I received an error message "failed to convert parameter value from a decimal to a datetime
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33530096
You need to find what the actual value of @today is coming across as to solve the issue. We cannot provide the correct syntax without knowing what that is for sure ... The other thing to check is if sched_date is actual a datetime in your system or is it stored as a decimal.
0
 

Author Comment

by:ptemo
ID: 33535164
Hello

sched_date is a datetime and the @today is just reading off a label box that is loaded with a page load event, that tells today's date.  Hope that helps.

thank you
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33535999
Yes, but what is the format. You need to ensure that it is coming over to SQL the way that you think it is. If this is .NET you may want to use the ToString("yyyy-MM-dd") as the format.
0
 

Author Comment

by:ptemo
ID: 33541287
Hello

I'm currently using this

date.Text = DateTime.Today.ToString("M/d/yyyy");

thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33541333
What does the SQL query look like from the VB.NET (SqlCommand)?

And try that as :

date.Text = DateTime.Today.ToString("yyyy-MM-dd");

Also :

Since you just want to use today's date anyway, try it like this in the SQL.


(sched_date < DATEDIFF(day, 0, GETDATE())+@aging)

Open in new window

0
 

Author Comment

by:ptemo
ID: 33552824
Hello mwvisa1,

Thanks for all your help.  When I tried your latest code I get an error message "String was not recognized as a valid datetime"

So I then tried putting 1/30/1900 instead of 30 days.  And it worked, however when I tried to view it in the browswer I get "Argument data type nvarchar is invalid for argument 2 of dateadd function. "

Do you have any thoughts? Do you think I should abandon this route, and try another method?

thanks again for all your help
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33552928
Did you try with current date using db functions as I showed? I really suspect you are having errors due to one system formatting the date as mm/dd/yyyy and other reading as dd/mm/yyyy but I may be wrong but it seems silly anyway if always passing today's date to not utilize the db's date and time as you avoid client specific language configuration differences which are what control the date format used by default.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question