.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> ASP.Net >> Post New QuestionBookmark and Share Subscribe to Forum

Date Subtraction in SQL

Posted By: pooja     Posted Date: August 17, 2010    Points:2   Category :ASP.Net
Hi frnds


I have column called TBL_PATIENT_ENTRY.RAND_DATE ... Here RAND_DATE is column in TBL_PATIENT_ENTRY its datatype is DateTime.

i want to have the following condition in SQL query..

(CurrentDate - TBL_PATIENT_ENTRY.RAND_DATE)/28 as whole number.

please give me SQL query to get this.....


thanks in advance


Responses
Author: Ramesh durai             
Posted Date: August 17, 2010     Points: 5   

Hi,


SELECT ABS(DATEDIFF(DD,GETDATE(),TBL_PATIENT_ENTRY.RAND_DATE))/28 FROM TBL_PATIENT_ENTRY

always it provide as whole number, no need to round function if you want use below query also


SELECT ROUND((ABS(DATEDIFF(DD,GETDATE(),TBL_PATIENT_ENTRY.RAND_DATE))/28),0) FROM TBL_PATIENT_ENTRY




Ramesh durai
Author: Suresh             
Posted Date: August 17, 2010     Points: 5   

Hello Pooja,

Kindly go through this code, U might get idea regarding this.
Declare @TBL_PATIENT_ENTRY table
(
RAND_DATE datetime
)

Insert into @TBL_PATIENT_ENTRY
Select Getdate()-60 Union all
Select Getdate()-100 Union all
Select Getdate()-11

--Select Getdate()+60 Union all
--Select Getdate()+100 Union all
--Select Getdate()+11


Select RAND_DATE,getdate() CurrentDate,
datediff(dd,getdate(),RAND_DATE) DaysDifference,
28 DivideBy,
abs(datediff(dd,getdate(),RAND_DATE)/28 )as Result
--cast(datediff(dd,getdate(),RAND_DATE)as int)/28 as Result
from @TBL_PATIENT_ENTRY t1





Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend