Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-08-25
18
Medium Priority
?
2,090 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

715 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