Thursday, 21 March 2019

Replace first occurrence of string in using Stuff in Date column SQl

Hi Friends,
Below is the scenario in which the date column had a blank value at the begining of date i,e instead of mm/dd/yyyy it is m/dd/yyyy . We have to replace the 0 value in m with 02 with blank 2 or space 2. but for months like 10,11,12 he value had to be retained, We have used case statement to handle this.





select * from table name u
where  u.Datecolumn between
case when left(convert (varchar,cast(@BeginDate as date),101),1) = '0' then
stuff(convert (varchar,cast(@BeginDate as date),101),1,1,' ') else
convert (varchar,cast(@BeginDate as date),101) end
And
case when left(convert (varchar,cast(@EndDate as date),101),1) = '0' then
stuff(convert (varchar,cast(@EndDate as date),101),1,1,' ') else
convert (varchar,cast(@EndDate as date),101) end

or

select * from table name u
where  u.Datecolumn  =
case when left(convert (varchar,cast('2018-02-01 11:37:45.167' as date),101),1) = '0' then
stuff(convert (varchar,cast('2018-02-01 11:37:45.167' as date),101),1,1,' ') else
convert (varchar,cast('2018-02-01 11:37:45.167' as date),101) end

No comments:

Post a Comment