Hard-coding a value but only for a limited time? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Hard-coding a value but only for a limited time?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-12-21 : 08:44:21

I have some code that works perfectly except it doesn't pick up one record that it should (due to something someone stupidly did to it....don't ask!) So I thought I'd hardcode it.

I thought I'd cracked it with a UNION to the following code


UNION ALL

SELECT CASE WHEN (CONVERT(CHAR(6), DATEADD(M, -13, GETDATE()), 112) <= '201111') THEN
'201111' END AS MONTH, 'FC57000' AS NCODE, '350.7' AS PL

which works but in January I don't want this record to appear as November 2011 will be more than 13 months prior.

I tried to simulate this by changing DATEADD(M, -13 to DATEADD(M, -12
but the record still appears, albeit with a NULL in the MONTH column.

What's the correct syntax?

TIA.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-21 : 08:49:29

What do you want to see in January - can you describe the rule you want to use given a date? Perhaps this?

UNION ALL 
SELECT CASE WHEN (CONVERT(CHAR(6), DATEADD(M, -13, GETDATE()), 112) <= '201111') THEN
'201111' ELSE '201211' END AS MONTH, 'FC57000' AS NCODE, '350.7' AS PL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 08:49:50

just add a condition like


...
UNION ALL

SELECT CASE WHEN (CONVERT(CHAR(6), DATEADD(M, -13, GETDATE()), 112) <= '201111') THEN
'201111' END AS MONTH, 'FC57000' AS NCODE, '350.7' AS PL
WHERE DATEPART(mm,GETDATE()) > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-12-21 : 08:50:53

quote:
Originally posted by sunitabeck

What do you want to see in January - can you describe the rule you want to use given a date? Perhaps this?
UNION ALL
SELECT CASE WHEN (CONVERT(CHAR(6), DATEADD(M, -13, GETDATE()), 112) <= '201111') THEN
'201111' ELSE '201211' END AS MONTH, 'FC57000' AS NCODE, '350.7' AS PL

I simply want the record to be ignored after that time so it shouldn't appear at all.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-21 : 08:52:20

Then what Visakh suggested

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-12-21 : 08:56:31

Thanks both, much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 08:56:56

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources