Trouble with date conversion

Question

Trouble with date conversion

jcobb 20350

Default port

Points: 1438
More actions
July 13, 2013 at 9:42 pm

#149844

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)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

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

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

jcobb 20350 Default port Points: 1438 More actions · Answer 2

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.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

remove the "SELECT"

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

shoestringdba SSCertifiable Points: 6226 More actions · Answer 4

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.

jcobb 20350 Default port Points: 1438 More actions · Answer 5

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.