DATE < > THAN 18 MONTHS IN A CASE STATEMENT

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

SQL Server 2012 Forums

Transact-SQL (2012)

DATE < > THAN 18 MONTHS IN A CASE STATEMENT

Author

Topic

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-04-24 : 15:07:31

i have a sales table for my employees. i would like a case statement to check to see if the most recent activity date is less that 18 months i would flag it as 'Not current' if the most recent activity date for the rep is greater than 18 months i would flag it as 'Current'. Please asvise

here is my pseudo
SELECT CASE WHEN (most recent)ACTIVITYDATE < 18 months then 'Not current'
when (most recent)ACTIVITYDATE > 18 months 'current' else 'N/A' END 'STATUS'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-24 : 15:26:03

Something like this maybe:

SELECT 
	Employee,
	CASE 
		WHEN MAX(ACTIVITYDATE) < DATEADD(MONTH, -18, CAST(SYSDATETIME() AS DATE)) THEN 'Not current' 
		ELSE 'current' 
	END AS STATUS
FROM
	TableName
GROUP BY
	Employee

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-04-24 : 17:11:45

Thanks for your help . when i run the select statement with the new case it works, however when i do my inserts i keep on getting
Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.

i checked my date coluns and they are date and datime datatypes.

WAmin
Starting Member

16 Posts

Posted - 2014-04-24 : 23:00:14

Are you inserting new line or updating?
Can you please show structure of table and your insert statement?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-24 : 23:52:52

quote:
Originally posted by Blessed1978

Thanks for your help . when i run the select statement with the new case it works, however when i do my inserts i keep on getting
Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.

i checked my date coluns and they are date and datime datatypes.

Please show us your INSERT query

KH
[spoiler]Time is always against us[/spoiler]

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-04-27 : 16:58:20

my datatype in one of my columns was off, it worked thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources