SQL Server 2005 Query Question

[PAz]

Hello

I need a little help with a SQL Server 2005 query I'm trying to do.

Basically I have a table that contains multiple columns, two of which are ARC_DOCMONTH and ARC_DOCYEAR that contain the current month and year respectively. The datatype of both is varchar.

I need to return all records that have the current month and year in those two columns. I thought I could do something like:

SELECT ARC_DOCINVOICENO, ARC_DOCDAT 
FROM CpArchiveIndex 
WHERE ARC_DOCMONTH = Month(NOW()) ORDER BY ARC_DOCDAT ASC

But I always get the error that Now() isn't a recognised a built in function.

Sorry, I'm not really much of a SQL guy. Thanks for any help

 

[PAz]

Great!!!!!! I got it working Thank you !

Now I have one question, the data for month/year comes from an ERP system. Sometimes the wrong data gets picked up, as shown by the following error;

'Conversion failed when converting the varchar value 'ubi' to data type int.'

Is there a way I can tell it to ignore conversion fails and build the query anyway?

 

[PAz]

I got it working with this:

SELECT GETDATE() AS 'GETDATE',ARC_DOCINVOICENO, ARC_DOCDAT, ARC_DOCMONTH, ARC_DOCYEAR
FROM CpArchiveIndex 
WHERE ARC_DOCMONTH = Month(GETDATE()) AND ARC_DOCCOMPANY = '00114'  
ORDER BY ARC_DOCDAT ASC

When I try to add

AND ARC_DOCYEAR = Year(GETDATE())

It always returns blank. Odd. Actually, I wonder if its because it returns the year as 2008 and we are storing it as 08

 

[PAz]

Sorry for the questions, should I be doing that here:

WHERE ARC_DOCMONTH = Month(GETDATE()) AND ARC_DOCYEAR = datepart(yy,GETDATE())

Or doing it at the beginning of the statement? Keeps returning blank again D:

 

[PAz]

Thanks for all the help this is working great now!