Simple Question

try this

if @dayadd = 0 
begin
newdatetime =  '2010/01/29 23:59:59'
end
else 
begin 
if @dayadd = 1 
begin
 newdatetime =  '2010/01/30 08:12:33'
end
end

Select allOpen in new window

PL. make it dynamic with relation to @datetime variable and it should be select statement.

In fact I have don this but I want to incorporate the logic in a complex sql query,

declare @dayadd int
declare @datetime smalldatetime
set @datetime = '2010/01/29 08:12:34'

set @dayadd = 0
select case when @dayadd = 0 then dateadd(d,1,convert(varchar,@datetime,112)) else dateadd(d,1,@datetime) end


set @dayadd = 1
select case when @dayadd = 0 then dateadd(d,1,convert(varchar,@datetime,112)) else dateadd(d,1,@datetime) end

you question doesn't seems clear to me. can you please elaborate more.

i don't understand what do you mean exactly,
you can use this query now?
I need more explain on question

if @dayadd = 0 
begin
@datetime =  '2010/01/29 23:59:59'
end
else 
begin 
if @dayadd = 1 
begin
 @datetime =  '2010/01/30 08:12:33'
end
end

Select allOpen in new window

sorry,this one is correct one

if @dayadd = 0 
begin
set @datetime =  '2010/01/29 23:59:59'
end
else 
begin 
if @dayadd = 1 
begin
 set @datetime =  '2010/01/30 08:12:33'
end
end

Select allOpen in new window

Hi experts

You would certainly understand what this code should do

declare @dayadd int
declare @datetime smalldatetime
set @datetime = '2010/01/29 08:12:34'

set @dayadd = 0
select case when @dayadd = 0 then dateadd(d,1,convert(varchar,@datetime,112)) else dateadd(d,1,@datetime) end


Pl. let me know.

I don't see any difference between both dateadd function, it is adding one day. just a different is both are using different format.

this means:

if @dayadd=0 then
select @datetime+1(day)
else
select @datetime+1(day) !

logic is as above,
but i think you need this:

declare @dayadd int
declare @datetime smalldatetime
set @datetime = '2010/01/29 08:12:34'

set @dayadd = 0
select case when @dayadd = 0 then dateadd(d,1,convert(varchar,@datetime,112)) else convert(varchar,@datetime,112) end

Select allOpen in new window

sorry , try this:
convert must be outer than dateadd function as below:

declare @dayadd int
declare @datetime smalldatetime
set @datetime = '2010/01/29 08:12:34'

set @dayadd = 0
select case when @dayadd = 0 then convert(varchar,dateadd(d,1,@datetime),112) else convert(varchar,@datetime,112) end

Select allOpen in new window

Hi RitesShah
<<I don't see any difference between both dateadd function,>>
In fact there is.

if @dayadd is 0 the result is 2010-01-30 00:00:00.000
if @dayadd is 1 the result is 2010-01-30 08:13:00.000

Let me know.

and at last, try this for adding @dayadd to your date

declare @dayadd int
declare @datetime smalldatetime
set @datetime = '2010/01/29 08:12:34'

set @dayadd = 0
select case when @dayadd <> 0 then convert(varchar,dateadd(Day,@dayadd,@datetime),112) else convert(varchar,@datetime,112) end

Select allOpen in new window

Hi reza_rad:

<<in your code, if @dayadd is 0 then convert(varchar,...,112) will select only date portion of your @datetime value, not time portion
but if @dayadd is 1 then there will be no convert(...) applied and therefor full date and time portions will returned>>

Exactly!!!

RitesShah, do you agree,

then I would come to real issue ( a complex query where a line needs to be modified just like this)

try this one:

declare @dayadd int
declare @datetime datetime
declare @Newdatetime datetime
set @datetime = '2010/01/29 08:12:34'
set @dayadd=0

if @dayadd = 0 
begin
	set @Newdatetime=convert(varchar,@datetime,111)+' 23:59:59'		
end
else 
begin
	set @Newdatetime=DATEADD(d,1,@datetime)
end
 select @Newdatetime

Select allOpen in new window

this will led correct result for you , i think

declare @dayadd int
declare @datetime smalldatetime
set @datetime = '2010/01/29 08:12:34'

set @dayadd = 0
select case when @dayadd <> 0 then dateadd(Day,@dayadd,@datetime) else @datetime end

Select allOpen in new window

Hi RiteShah

Your last code indicates that You have Understood what I want.

wait''

and if you dont' want time in first instance but want time in second instance (else part), use this code.

declare @dayadd int
declare @datetime datetime
declare @Newdatetime datetime
set @datetime = '2010/01/29 08:12:34'
set @dayadd=0

if @dayadd = 0 
begin
        set @Newdatetime=convert(varchar,@datetime,111)           
end
else 
begin
        set @Newdatetime=DATEADD(d,1,@datetime)
end
 select @Newdatetime

Select allOpen in new window

Pl. look at

https://www.experts-exchange.com/questions/25096629/Either-remain-between-same-day-or-next-day.html?fromWizard=true

and change line no 38.

there
@dayadd is equivalend to
 (select dayadd from cte_week sw where sw.card_code = y.emp_code)
and @datetime is equivalent to
e.datetime

It's better to identify what the result you want exactly , instead of guessing me and RiteshShah for the expected results !!!

Does this help?
1. Create a function
CREATE FUNCTION setdate(@dayadd int)
returns datetime
as
begin
set @dayadd = @dayadd
declare @datetime datetime
if @dayadd = 0
begin
set @datetime='2010/01/29 23:59:59'
end
if @dayadd= 1
begin
      set @datetime= '2010/01/30 08:12:33'
end
return @datetime
end
2. Each time you need use function, you can use
declare @datetimea datetime;
set @datetimea='2010/01/29 08:12:34';
select dbo.setdate(1);

Hi reza_rad,

Sorry I could not ask my question clearly.

Pl. consider this thread closed.

Pl. look at https://www.experts-exchange.com/questions/25096629/Either-remain-between-same-day-or-next-day.html?fromWizard=true

there at line no 38 I want that the date of e2.datetime should be same or e2.datetime+1 based on the column dayadd in the table cte_week.

<>

Exactly, but please implement this in a complex query

https://www.experts-exchange.com/questions/25096629/Either-remain-between-same-day-or-next-day.html?fromWizard=true

try this one in your where query

(cte_week.dayadd=0 and e2.[datetime] between e.[datetime] and dateadd(ss,-1,dateadd(Day,1,e.[datetime]))   )
or 
(cte_week.dayadd=0 and e2.[datetime] between e.[datetime] and dateadd(Day,1,e.[datetime])   )

Select allOpen in new window