Determined Date range - SQL Server Forums

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

Transact-SQL (2008)

Determined Date range

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-03-12 : 01:18:56

Hi,

I hope somebody can help me. Thank you.

1. I have a confirmed date column in my table that i need to determined if that date is within the range. if within in range i have to put a remarks.
2. if the date format is '1/1/1900' and '1/1/2099' the remarks is 'NO ETA'
3. The year should be dynamic.

Given is the sample data.

Drop table #sample

Create Table #sample
(ConfirmedDate datetime)
insert into #sample(ConfirmedDate) values ('1/1/1900')
insert into #sample(ConfirmedDate) values ('1/1/2099')
insert into #sample(ConfirmedDate) values ('11/29/2013')
insert into #sample(ConfirmedDate) values ('12/30/2013')
insert into #sample(ConfirmedDate) values ('4/1/2014')
insert into #sample(ConfirmedDate) values ('3/12/2014')
insert into #sample(ConfirmedDate) values ('5/1/2014')
insert into #sample(ConfirmedDate) values ('2/24/2014')
insert into #sample(ConfirmedDate) values ('2/14/2014')
insert into #sample(ConfirmedDate) values ('4/25/2014')
insert into #sample(ConfirmedDate) values ('5/1/2014')
insert into #sample(ConfirmedDate) values ('5/10/2014')
insert into #sample(ConfirmedDate) values ('6/20/2014')
insert into #sample(ConfirmedDate) values ('6/25/2014')
insert into #sample(ConfirmedDate) values ('7/20/2014')
insert into #sample(ConfirmedDate) values ('8/25/2014')
insert into #sample(ConfirmedDate) values ('8/20/2014')
insert into #sample(ConfirmedDate) values ('10/30/2014')
insert into #sample(ConfirmedDate) values ('11/20/2014')
insert into #sample(ConfirmedDate) values ('12/10/2014')
insert into #sample(ConfirmedDate) values ('11/24/2014')
insert into #sample(ConfirmedDate) values ('12/25/2014')


select
   ConfirmedDate,
   Case When ConfirmedDate ='1900-01-01 00:00:00.000' OR ConfirmedDate ='2099-01-01 00:00:00.000' Then 'NO ETA'
   End As Remarks
from #sample

Sample Date Range


REMARKS---SAMPLE DATE RANGE
-------------------------------
NO ETA	--Previous	
JAN ETA	--12/21/2013 1/20/2014
FEB ETA	--1/21/2014	2/20/2014
MAR ETA	--2/21/2014	3/20/2014
APR ETA	--3/21/2014	4/20/2014
MAY ETA	--4/21/2014	5/20/2014
JUN ETA	--5/21/2014	6/20/2014
JUL ETA	--6/21/2014	7/20/2014
AUG ETA	--7/21/2014	8/20/2014
SEP ETA	--8/21/2014	9/20/2014
OCT ETA	--9/21/2014	10/20/2014
NOV ETA	--10/21/2014 11/20/2014
DEC ETA	--11/21/2014 12/20/2014
NO ETA	--1/1/1900	
NO ETA	--1/1/2099

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-12 : 02:23:05

[code]
case when ConfirmedDate = '1900-01-01' then 'NO ETA'
when ConfirmedDate = '2099-01-01' then 'NO ETA'
when right(convert(varchar(10), ConfirmedDate , 112), 4) >= '1221'
or right(convert(varchar(10), ConfirmedDate , 112), 4) <= '0120' then 'JAN ETA'
when right(convert(varchar(10), ConfirmedDate , 112), 4) between '0121' and '0220' then 'FEB ETA'
when right(convert(varchar(10), ConfirmedDate , 112), 4) between '0221' and '0320' then 'MAR ETA'
.....
end
[/code]

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-03-12 : 20:15:35

Thank you very much kthan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources