Validate the date in sql server

Question

0.00/5 (No votes)

See more:

I am storing leaves in a table where the start date and end date are only date part and the time is stored as 'FN' and 'AN' for forenoon and after noon,

for example storing a full day leave is as below

Fromdate : 2016-03-03, fromLeaveTime : 'Full Day'
Todate: 2016-03-03, toLeaveTime:'FN'

fromLeaveTime and toLeaveTime may contain values 'FN', 'AN' or 'Full Day'

how to validate whether a leave is already applied?

if a person apply for half day how can I validate?

sample data is as follows

C#

Id	UserId	FromDate	ToDate		Totaldays		FromLeaveTime	ToLeaveTime
78	118		2016-03-03	2016-03-03	1				Full Day		Full Day
79	118		2016-03-04	2016-03-04	0.5				FN				FN
80	118		2016-03-07	2016-03-07	0.5				AN				AN
81	118		2016-03-08	2016-03-09	1.5				Full Day		FN
82	118		2016-03-10	2016-03-11	1				AN				FN

What I have tried:

SQL

IF EXISTS(SELECT * FROM WorkFromHome WHERE UserId=@Id AND ((@FromDate between FromDate and ToDate) OR (@ToDate between FromDate and ToDate)))
	 BEGIN
	 
	 
	 
	 RAISERROR('Already Applied',16,1);
	 return;
	  END

Posted 2-Mar-16 17:59pm

Jijutj

Updated 7-Mar-16 23:26pm

v5

Add a Solution

Comments

[no name] 3-Mar-16 0:20am

You want to check "half day validation with @FromDate & @EndDate" or it will check differently?

Jijutj 3-Mar-16 2:42am

I have updated the question with sample data,

Maciej Los 3-Mar-16 5:34am

The question is still not clear...
Remember, we can't read in your mind or direct from your screen. We haven't even an access to your HDD. So, be more specific and provide more details about your issue.

Maciej Los 3-Mar-16 1:45am

Is there any primary key, which determines what 'job' has already been started?
Please, Improve question and provide more details.

1 solution

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)

Er. Puneet Goel · Answer 1 · 2016-03-02T18:50:00

Solution 1

Suppose You have following data in database)
From :03-03-2016 To:04-03-2016
Then your query will be like

SQL

IF EXISTS(SELECT * FROM WorkFromHome WHERE UserId=@Id AND ((@FromDate >= FromDate and ToDate) OR (@ToDate <= FromDate and ToDate)))
BEGIN
	 RAISERROR('Already Applied',16,1);
	 return;
END

As it can be the fromdate itself, so between won't work.

Let me know if you have further query.

Posted 2-Mar-16 18:50pm

Er. Puneet Goel

Comments

Jijutj 3-Mar-16 2:32am

I have updated the question with sample data

Er. Puneet Goel 3-Mar-16 2:38am

Can you please tell a scenario where my query fails so that i can provide better solution.

Jijutj 3-Mar-16 2:52am

suppose I have added a leave from 2016-03-04 FN to 2016-03-04 FN means half day, I need to add AN (after noon ) leave

Er. Puneet Goel 3-Mar-16 4:05am

Will it always me either AN or FN incase From and to date are same ?