Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trouble with date conversion Expand / Collapse
Author
Message
Posted Saturday, July 13, 2013 9:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 6:32 AM
Points: 7, Visits: 16
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)
Post #1473335
Posted Sunday, July 14, 2013 9:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 1,943, Visits: 20,180
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
Post #1473361
Posted Monday, July 15, 2013 6:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 6:32 AM
Points: 7, Visits: 16
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.
Post #1473611
Posted Monday, July 15, 2013 6:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 1,943, Visits: 20,180
remove the "SELECT"



______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1473612
Posted Monday, July 15, 2013 6:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 5:55 AM
Points: 155, Visits: 285
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.
Post #1473613
Posted Monday, July 15, 2013 7:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 6:32 AM
Points: 7, Visits: 16
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.
Post #1473655
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse