find date difference

Posted by Klbaiju under Sql Server on 4/12/2010 | Views : 1472 | Status : [Member] | Replies : 6
Hi,

i want to find the datedifference between 2 dates
my format is dd/mm/yyyy

this is my problem

begin
declare @Days int
set @Days=1
select substring(intourpackagefromtodates,0,dbo.LastIndex(intourpackagefromtodates,'-')) as date1 from tourdetailsnew where vctourcode='bj-456' and intourday='2'
select substring(intourpackagefromtodates,dbo.LastIndex(intourpackagefromtodates,'-')+1,len(intourpackagefromtodates)-(dbo.LastIndex(intourpackagefromtodates,'-'))) as date2 from tourdetailsnew where vctourcode='bj-456' and intourday='2'
@Days=select datediff(date2,date1)+1
end

it showing error
if i avoid the @days line
the output is
date1
21/04/2010
date2
23/04/2010

i want the the output should be 3.

how it is possible

thanks in advance

Regards
K L BAIJU




Responses

Posted by: Vuyiswamb on: 4/12/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Look at how i did and take it from there

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME
SET @StartDate ='2010-01-10'
SET @EndDate ='2010-01-15'
SELECT DATEDIFF(day, @StartDate, @EndDate) AS [Days Difference]


Thank you for posting at Dotnetfunda
[Administrator]

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 4/12/2010 [Member] Starter

Up
0
Down
my date format is dd/mm/yyyy
that is the problem


Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 4/12/2010 [Member] Starter

Up
0
Down
SELECT DATEDIFF(day, 23/04/2010, 21/04/2010) AS [Days Difference]

this showing error

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 4/12/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
What Error are you getting ?

Thank you for posting at Dotnetfunda
[Administrator]

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 4/12/2010 [Member] Starter

Up
0
Down
this is my query

begin


select substring(intourpackagefromtodates,0,dbo.LastIndex(intourpackagefromtodates,'-')) as date1 from tourdetailsnew where vctourcode='bj-456' and intourday='2'
select substring(intourpackagefromtodates,dbo.LastIndex(intourpackagefromtodates,'-')+1,len(intourpackagefromtodates)-(dbo.LastIndex(intourpackagefromtodates,'-'))) as date2 from tourdetailsnew where vctourcode='bj-456' and intourday='2'
select datediff(d,date2,date1)+1 as days
end

error is


Msg 207, Level 16, State 1, Line 7
Invalid column name 'date2'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'date1'.


select substring(intourpackagefromtodates,0,dbo.LastIndex(intourpackagefromtodates,'-')) as date1 from tourdetailsnew where vctourcode='bj-456' and intourday='2'

this query returns
23/04/2010
select substring(intourpackagefromtodates,dbo.LastIndex(intourpackagefromtodates,'-')+1,len(intourpackagefromtodates)-(dbo.LastIndex(intourpackagefromtodates,'-'))) as date2 from tourdetailsnew where vctourcode='bj-456' and intourday='2'
this return
21/04/2010
if i avoid
select datediff(d,date2,date1)+1 as days

iam struck in section


Regards
K L BAIJU




Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 4/12/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
if date2 and date1 are variables then it means you need to add @ sign before the name of the variable to be like this
[code]
@date1

or

@date2
[/code]

Thank you for posting at Dotnetfunda

Vuyiswa Maseko


Thank you for posting at Dotnetfunda
[Administrator]

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response