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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Determined Date range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

441 Posts

Posted - 03/12/2014 :  01:18:56  Show Profile  Reply with Quote
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)

Singapore
17655 Posts

Posted - 03/12/2014 :  02:23:05  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

441 Posts

Posted - 03/12/2014 :  20:15:35  Show Profile  Reply with Quote
Thank you very much kthan.
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