March 27, 2014 at 7:43 pm
hi firends hi small doubt in sql server please tell me how to solve this
issue.
table :emp
fromdate and enddate both columns datatype varchar.
id| fromdate | Enddate
1| 2014-03-20 | 2014-03-25
2| 2014-03-15 |
3| 2014-02-24 | 2014-02-27
4| 2014-03-10 |
5| 2014-02-13 |
6| 2014-03-01 | 2014-03-10
based on this i want output like below
id | Noofdays
1 | 5
2 | 0
3 | 3
4 | 0
5 | 0
6 | 9
here i need to get noof days between from and endate .
when enddate is empty or null then we treated as noof days is 0 days.
and i tried like below
select
datediff(day,fromdate,(CASE WHEN coalesce(ltrim(rtrim([nddate])),'') = '' THEN '0' ELSE ltrim(rtrim([emrenddate])) END)
) from emp
but its showing error please tell me how to solve issue in sql server.
March 27, 2014 at 9:26 pm
Given the following ddl:
declare @t table
(
id int
,fromDate varchar(10)
,endDate varchar(10)
)
insert @t (id, fromDate, endDate)
values
(1, '2014-03-20','2014-03-25')
,(2, '2014-03-15','')
,(3, '2014-02-24','2014-02-27')
,(4, '2014-03-10',NULL)
,(5, '2014-02-13',' ')
,(6, '2014-03-01','2014-03-10')
This seemed to work for the above data.
select fromdate, enddate,
DATEDIFF(dd, fromDate,
case when endDate is null or LEN(endDate) = 0 then fromDate
else endDate end)
from @t
Of course if you can change the datatype to date or datetime, then you would not have to worry about empty strings, spaces or other alpha numeric characters in the column. It would be either a date or null.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply