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