i want to write a subquery where i can write a new column by subtracting earlier 2?

Question

0.00/5 (No votes)

See more:

SQL

select  (select dtleavefrom from dilpreet.leavedetails minus select dtleaveto from dilpreet.leavedetails) as newcol from dilpreet.leavedetails

Posted 5-Jul-13 2:05am

a2ulthakur

Add a Solution

Comments

Maciej Los 8-Jul-13 5:09am

What are you trying to achieve?
Do you want to get the difference between dates?
Please, be more specific and provide more details.

4 solutions

Solution 2

Hi,

you can use the following for your purpose

SQL

create table test (fromDate datetime, Todate DateTime)

insert into test values('15-july-2013','16-july-2013')
insert into test values('1-july-2013','10-july-2013')

select *,DATEDIFF(day,fromDate,Todate)+1 as LeavesBetweenRangefrom test

I have added +1 because i am including both the days when counting no. of leaves

Posted 5-Jul-13 2:10am

Adarsh chauhan

Updated 5-Jul-13 2:25am

v3

Solution 3

select (dtleaveto-dtleavefrom) as newcol from dilpreet.leavedetails

Note :- i think dtleaveto is at first Place in above statement.

Posted 5-Jul-13 2:13am

Atul Kumar Singh From Pune

Solution 4

Hi Frnd,

SQL

select dtleavefrom,dtleaveto,(dtleaveto-dtleavefrom) newcol
from dilpreet.leavedetails

Use the above script.

Posted 7-Jul-13 21:02pm

Member 10024564

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)

Kuthuparakkal · Accepted Answer · 2013-07-05T02:07:00

Solution 1

SQL

select (dtleavefrom - dtleaveto ) as newcolumn
from dilpreet.leavedetails

Posted 5-Jul-13 2:07am

Kuthuparakkal

Comments

Adarsh chauhan 5-Jul-13 8:24am

sorry to say but It would give wrong output as you can see that dtleavefrom and dtleaveto must be date.

Kuthuparakkal 5-Jul-13 8:29am

did user tell you that ? Let him comeback with proper datatypes..
I am very poor in guessing the datatypes used by user; no super-natural abilities, sorry!

Adarsh chauhan 5-Jul-13 8:37am

ya you are right user has not mentioned any datatype, but just said because it is but obvious that when we talk about leavefrom and leave to we talk about days.
above will work with numeric data.

I didn't mean to be rude..

Kuthuparakkal 5-Jul-13 12:05pm

I was making my point, no offence... carry on..