Where date in column equals current month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Where date in column equals current month

Author

Topic

GMD1975
Starting Member

3 Posts

Posted - 2012-09-06 : 06:32:13

Hi,

I need to run a query that counts values where the date in CNG.ACT_END_DATE is equal to whatever the current month happens to be but I keep getting errors.... Can anyone help me please?

Kind Regards
Gerard

WHERE (CNG.ACT_END_DATE = MONTH(date)=@thismonth)

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-09-06 : 06:43:48

where month(cng.acct_date) in (select MONTH(GETDATE()));

GMD1975
Starting Member

3 Posts

Posted - 2012-09-06 : 06:51:23

quote:
Originally posted by ahmeds08

where month(cng.acct_date) in (select MONTH(GETDATE()));

Hi, Thanks for replying, I've tried that but get....

Invalid object name '_SMDBA_._CHANGE_'.

Here's the full query

SELECT SEV.NAME as Severity
, COUNT (*) as Outstanding
FROM _SMDBA_._CHANGE_ CNG WITH (NOLOCK)
LEFT OUTER JOIN _SMDBA_._SEVERITY_ SEV WITH (NOLOCK) ON CNG.SEQ_SEVERITY = SEV.SEQUENCE
LEFT OUTER JOIN _SMDBA_._SUBJECTS_ SUB WITH (NOLOCK) ON CNG.SUBJECT = SUB.SEQUENCE
WHERE MONTH (CNG.ACT_END_DATE) in (SELECT MONTH(GETDATE()))
AND SEV.NAME IS NOT NULL
GROUP BY SEV.NAME
ORDER BY 1

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-09-06 : 06:54:17

select * from sys.objects where upper(object_name)=upper('_SMDBA_._CHANGE_ CNG ');

exec this query and see if you are getting a record.
if you dont get anything then the table name you provided is wrong or not created in the databse.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 07:40:11

If this is MS SQL Server then try to change

_SMDBA_._CHANGE_ to [_SMDBA_].[_CHANGE_]

and
_SMDBA_._SEVERITY_ to [_SMDBA_].[_SEVERITY_]

and

_SMDBA_._SUBJECTS_ to [_SMDBA_].[_SUBJECTS_]

Too old to Rock'n'Roll too young to die.

GMD1975
Starting Member

3 Posts

Posted - 2012-09-06 : 08:04:18

quote:
Originally posted by ahmeds08

select * from sys.objects where upper(object_name)=upper('_SMDBA_._CHANGE_ CNG ');

exec this query and see if you are getting a record.
if you dont get anything then the table name you provided is wrong or not created in the databse.

Hi, Your line works! User error! I wasnt pointing to the right database in SQL server... Many thanks for all your help chaps

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources