July 13, 2013 at 9:42 pm
I want to match the year field in a table and pull out all that are a year old. I'm trying to use the GETDATE and look back 1 year but I'm missing something in the conversion. Here's my script. I get an error "Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric."
SELECT ih.customer_id
, ih.invoice_no
FROM invoice_hdr ih
WHERE ih.period = 12
AND ih.year_for_period = CONVERT(varchar(10),DATEADD(YEAR, -1, GETDATE()),101)
July 14, 2013 at 9:25 am
jcobb 20350 (7/13/2013)
I want to match the year field in a table and pull out all that are a year old. I'm trying to use the GETDATE and look back 1 year but I'm missing something in the conversion. Here's my script. I get an error "Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to numeric."
SELECT ih.customer_id
, ih.invoice_no
FROM invoice_hdr ih
WHERE ih.period = 12
AND ih.year_for_period = CONVERT(varchar(10),DATEADD(YEAR, -1, GETDATE()),101)
any good?
ih.year_for_period = DATEPART(yy,DATEADD(YEAR, -1, GETDATE()))
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 15, 2013 at 6:15 am
No. Now I get the error "Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'SELECT'." which I'm sure has to do with the SELECT in your suggestion.
July 15, 2013 at 6:17 am
remove the "SELECT"
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 15, 2013 at 6:22 am
I'm kind of curious why you're converting to a character string. The error message seems to indicate the ih.year_for_period field is numeric.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
July 15, 2013 at 7:48 am
J Livingston SQL (7/15/2013)
remove the "SELECT"
Perfect. That's what I wanted, just couldn't figure it out without a little help. Thanks.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy