SQL Server 2005 Query Question

PAz

PAz

PAz

Soldato
Joined
18 Oct 2002
Posts
6,567
Location
Beds
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:

Code: 
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
 
From a quick Google, I found this page here (Example B), I think you'll first need to get the current system date/time, using one of these examples:

Code: 
SELECT SYSDATETIME() AS 'SYSDATETIME'

--Results
--SYSDATETIME
--2007-10-22 14:10:41.7984554
--(1 row(s) affected)

SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET'

--Results
--SYSDATETIMEOFFSET
--2007-10-22 14:11:34.2607172 -0
--(1 row(s) affected)

SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME'

--Results
--SYSUTCDATETIME
--2007-10-22 21:12:11.7069470
--(1 row(s) affected)

SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP'

--Results
--CURRENT_TIMESTAMP
-------------------------
--2007-10-22 14:12:33.320
--(1 row(s) affected)

SELECT GETDATE() AS 'GETDATE'

--Results
--GETDATE
--2007-10-22 14:13:57.943
--(1 row(s) affected)

SELECT GETUTCDATE() AS 'GETUTCDATE'

--Results
--GETUTCDATE
--2007-10-22 21:14:35.657
--(1 row(s) affected)

Then use that in your previous command in place of "Now()"
 
Last edited:
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?
 
To expand on what Confused said, GetDate() is my preferred way of doing things.

So, Month(GetDate()) for today would return an integer, 10.

If you require the name of the month, try something like DateName(month, GetDate()).

Edit: Woops, bit too late.
 
I got it working with this:

Code: 
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
Code: 
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
 
Sorry for the questions, should I be doing that here:

Code: 
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:
 
just run select month(getdate()) and select datepart(yy,GETDATE())
to debug why its not returning any rows

Check out books online for SQL server as they usually have a load of examples as well.
 
PAz said:
Sorry for the questions, should I be doing that here:

Code: 
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:
Click to expand...


Well, it turns out I'm a bit of a tool. datepart(yy,GETDATE()) will give you 2008, as well. :o

What you actually want, to return "08", is something like right(datepart(yy, GetDate()), 2).
 
You must log in or register to reply here.
Back
Top Bottom