How to increase date in table

Question

1.00/5 (1 vote)

See more:

SQL

Case when EXISTS(SELECT * FROM holiday_list WHERE [DAY] = @date AND [YEAR] = YEAR(@date)) then DATEADD(D,1,@Actual_Leave_To)

actullay this is my case holiday_list is my table where public holiday show in this table suppose holiday date is 25/12/15 and we execute this case then this date add and show date 26/12/15

but i want to know when i declare 24/12/15 then it will show 26/12/15 means it understand next day is 25/12/15 this is public holiday which is in my table and date show 26/12/15

please helpme

Posted 22-Dec-15 17:35pm

Member 12183079

Updated 23-Dec-15 0:06am

phil.o

v4

Add a Solution

Comments

CHill60 23-Dec-15 5:12am

What do you mean by "not working"? And some sample data and expected results would be useful.
What data type is column [DAY]? If it is a Date then this would only return a single entry for [DAY]=@date ... the [YEAR] check is irrelevant

CHill60 23-Dec-15 8:03am

What data type is the column [DAY] ?

ZurdoDev 23-Dec-15 12:45pm

What exactly is your question? What is the problem with the sql you have?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sandeepmittal11 · Answer 1 · 2016-01-02T20:34:00

Solution 2

while loop is implemented to handle the case of consecutive public holidays

SQL

create table #holiday_list (hol_date date, hol_desc varchar(100))
insert into #holiday_list
values('12/25/2015', 'Christmasc Day')

declare @Actual_Leave_To date
set @Actual_Leave_To='12/25/2015'

while(select count(1) from #holiday_list where hol_date = @Actual_Leave_To)>0
begin
	set @Actual_Leave_To = DATEADD(d,1,@Actual_Leave_To)
end  
select @Actual_Leave_To
drop table #holiday_list

Posted 2-Jan-16 20:34pm

sandeepmittal11

Comments

Member 12183079 6-Jan-16 4:40am

Yes This code is running but you set @Actual_Leave_To='12/25/2015'this date then how to understand when we declare '12/24/2015 this day then it will understand net day is 12/25/2015' and show 12/26/2015'

sandeepmittal11 7-Jan-16 0:21am

I have posted another solution. Check if you are looking for that...

sandeepmittal11 · Answer 2 · 2016-01-06T18:20:00

Solution 3

SQL

create table #holiday_list (hol_date date, hol_desc varchar(100))
insert into #holiday_list
values('12/25/2015', 'Christmasc Day')
 
declare @Actual_Leave_To date, @Next_Day date
set @Actual_Leave_To='12/24/2015'
set  @Next_Day=dateadd(d,1,@Actual_Leave_To)
 
while(select count(1) from #holiday_list where hol_date = @Next_Day)>0
begin
    set @Next_Day = DATEADD(d,1,@Next_Day)
end  
select @Next_Day
drop table #holiday_list

Posted 6-Jan-16 18:20pm

sandeepmittal11

Comments

CHill60 7-Jan-16 10:58am

Please don't post more than one solution to the same question - how is a reader supposed to know which one is meant to be the solution? Use the Improve question link if you want to add or change information, or delete your earlier solution if it is incorrect

sandeepmittal11 9-Jan-16 0:08am

I have provided different solutions (logically) as per user's requirement.
Don't know which one would fit up or something else is required.
What shall we do in that case?

CHill60 9-Jan-16 10:03am

I usually discuss the alternatives within a single post

Matt Comb · Answer 3 · 2016-01-02T12:42:00

Solution 1

Why don't you just do it something like this:

UPDATE holiday_list
SET D = DATEADD(D,1,@Actual_Leave_To)
where ID in (SELECT ID FROM holiday_list WHERE [DAY] = @date AND [YEAR] = YEAR(@date))

Posted 2-Jan-16 12:42pm

Matt Comb