Solved

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

Posted on 2010-08-25
18
2,045 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL trigger 5 51
Many to one in one row 2 48
SQL works but want to get the XML node data separately 11 57
SQL 2008 - Disappearing Temp Table in Stored Procedure 24 64
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

752 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