cursor or ? - 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

General SQL Server Forums

New to SQL Server Programming

cursor or ?

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-02-19 : 17:05:42

I have following sample code


declare @temp table
(date varchar(8)
)

insert into @temp
select '021075' union all
select '210223' union all
select '401098' union all
select '121092' union all
select '010207'

select
case when
(select substring(date,1,2) from @temp) <= 31

then
(
	select
	date
	,substring(date,1,2) as d
	,substring(date,3,2) as m
	,substring(date,5,2) as y
	,case when substring(date,5,2) between 10 and 99 
			then cast('19' + substring(date,5,2) as varchar(4)) 
			else cast('20' + substring(date,5,2) as varchar(4)) end as y_full
	,cast(
	case when substring(date,5,2) between 10 and 99 
			then cast('19' + substring(date,5,2) as varchar(4)) 
			else cast('20' + substring(date,5,2) as varchar(4)) end
	+ '-' + cast(substring(date,3,2) as varchar(4)) --m
	+ '-' + cast(substring(date,1,2) as varchar(4)) --d
	as smalldatetime) date2
	from @temp
)
else
print 'NULL'
end

and i want query to read all 4 lines, but it actually stops at 2, because next line (3 line) has a false result when casting into date.

I would like for line 3 (false date), query to return value "NULL" or something, but would like to query to execute till the end.

How can i integrate cursor? and/or Case...when statement?

thank you

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 17:17:22

This ??


declare @temp table
(date varchar(8)
)

insert into @temp
select '021075' union all
select '210223' union all
select '401098' union all
select '121092' union all
select '010207'
--substring(date,1,2) <= 31


select
	date 
	,case when substring(date,1,2) <= 31 then substring(date,1,2) else null end as d
	,case when substring(date,1,2) <= 31 then substring(date,3,2) else null end as m
	,case when substring(date,1,2) <= 31 then substring(date,5,2) else null end as y
	,case when substring(date,1,2) <= 31 
	then 
	case when substring(date,5,2) between 10 and 99 then cast('19' + substring(date,5,2) as varchar(4)) 
	else cast('20' + substring(date,5,2) as varchar(4)) end 
	else null 
	end 
	as y_full
	,case when substring(date,1,2) <= 31 then cast(
	case when substring(date,5,2) between 10 and 99 then cast('19' + substring(date,5,2) as varchar(4)) 
	else cast('20' + substring(date,5,2) as varchar(4)) 
	end
	+ '-' + cast(substring(date,3,2) as varchar(4)) --m
	+ '-' + cast(substring(date,1,2) as varchar(4)) --d
	as smalldatetime) else null end date2
from 
	@temp

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-02-19 : 17:34:01

works fine :) great. thank you

any ideas how to solve this with cursor?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-02-19 : 23:52:51

Why do you want to use a cursor?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources