?
Solved

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

Posted on 2010-08-25
18
Medium Priority
?
2,149 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
  • 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 61

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 61

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
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 

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 61

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 61

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 61

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 61

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 61

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 61

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 61

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 61

Accepted Solution

by:
Kevin Cross earned 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Detach & Attach 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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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