Problem with eliminate convert date over 30 days

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 2005 Forums

Transact-SQL (2005)

Problem with eliminate convert date over 30 days

Author

Topic

maximation
Starting Member

6 Posts

Posted - 2007-11-07 : 17:31:53

My requirement is following
If Employee Status = (Active or Inactive) Status Date should be Current Dates
If Employee Status = (Terminated) Status Date should be Termination_Date (only last 30 days)

I need to eliminate any termination records over the last 30 days. In this case I don’t need the record from Sep 20. The terminate_Date column’s data_type is decimal coming from csv file and I’m converting this into datetime datatype and showing as Status_date. My query is followed by the results. when I added this statment in the where clause terminate_date >=( CASE WHEN [EMPLOYEE_STATUS]='Terminate' THEN DATEADD(day,-30,GETDATE()) ELSE Terminate_date END)
it gave me an error that Arithmetic overflow error converting expression to data type datetime."

EMPSTATUS - STATUS_DATE - TERMINATE_DATE

Inactive 2007-11-08 0
Terminate 2007-09-20 20070920
Terminate 2007-10-26 20071026
Active 2007-11-08 0
Active 2007-11-08 0

select
[EMPLOYEE_STATUS],
CASE WHEN [EMPLOYEE_STATUS]='Active'
THEN (SELECT DATEADD(day, 1, getdate()))
WHEN [EMPLOYEE_STATUS]=’Inactive’
THEN (SELECT DATEADD(day, 1, getdate()))
WHEN [EMPLOYEE_STATUS]='Terminate'
THEN( CONVERT( datetime,cast (hr.terminate_date as varchar ) , 110)) END AS [Status_Date]
from hr where terminate_date >=( CASE WHEN [EMPLOYEE_STATUS]='Terminate' THEN DATEADD(day,-30,GETDATE()) ELSE Terminate_date END)

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 17:36:12

have you tried a where clause like this...

WHERE
([EMPLOYEE_STATUS]='Terminate' AND terminate_date > GETDATE() - 30)
OR
([EMPLOYEE_STATUS]<>'Terminate')

maximation
Starting Member

6 Posts

Posted - 2007-11-07 : 17:43:22

This gives me the same error "Arithmetic overflow error converting expression to data type datetime"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-07 : 17:43:52

Datediff may work better...


Select [EMPLOYEE_STATUS], 
        Case [EMPLOYEE_STATUS]
               WHEN in ('Active','Inactive') THEN dateadd(day,1,getdate())
               WHEN 'Terminate' THEN cast(hr.Terminate_date as datetime)
         END as Status_Date
FROM hr 
WHERE Employee_Status != 'Terminate' OR datediff(d,Terminate_Date,getdate()) >=30

Is there a join or a table missing from your sample?

your cast(hr.Terminate_date as varchar) should just look like Convert(datetime,terminate_date,110) perhaps.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 17:57:14

my mistake didn't notice that terminate_date is not a DATE...
WHERE
([EMPLOYEE_STATUS]='Terminate' AND terminate_date > CONVERT(char(8), GETDATE() - 30, 112)
OR
([EMPLOYEE_STATUS]<>'Terminate')

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-11-07 : 18:03:05

Am I understanding this correctly?


declare @hr table
	(EMPLOYEE_STATUS varchar(10), Status_Date datetime, Terminate_Date decimal)

insert into @hr
	select 'Inactive', '2007-11-08', 0 union
	select 'Terminate', '2007-09-20', '20070920' union
	select 'Terminate', '2007-10-26', '20071026' union
	select 'Active', '2007-11-08', 0 union
	select 'Active', '2007-11-08', 0


select	[EMPLOYEE_STATUS],
		CASE WHEN [EMPLOYEE_STATUS]='Active'
		THEN DATEADD(day, 1, getdate())
		WHEN [EMPLOYEE_STATUS]='Inactive'
		THEN DATEADD(day, 1, getdate())
		WHEN [EMPLOYEE_STATUS]='Terminate'
		THEN( CONVERT( datetime, terminate_date, 110)) END AS [Status_Date]
from	(select	Employee_Status [Employee_Status], 
			Status_Date	[StatusDate], 
			cast(nullif(Terminate_date, '0') as varchar) [Terminate_date]
	from	@hr
		) d
where	terminate_date >=( CASE WHEN [EMPLOYEE_STATUS]='Terminate' THEN DATEADD(day,-30,GETDATE()) ELSE Terminate_date END)	
	or terminate_date is null

edit: removed extra cast

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources