SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Hard-coding a value but only for a limited time?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
172 Posts

Posted - 12/21/2012 :  08:44:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/21/2012 :  08:49:29  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/21/2012 :  08:49:50  Show Profile  Reply with Quote
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/

Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
172 Posts

Posted - 12/21/2012 :  08:50:53  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/21/2012 :  08:52:20  Show Profile  Reply with Quote
Then what Visakh suggested
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
172 Posts

Posted - 12/21/2012 :  08:56:31  Show Profile  Reply with Quote
Thanks both, much appreciated.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/21/2012 :  08:56:56  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000