Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Urgent help need to verify DateDiff is correct? Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 2:35 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Hi Friends,

my requirement is-

"SmartLaborII Work Order End Date" is NOT greater than "SmartLaborII Previous Work Order End Date"]-

and the code I'm using is-

//

datediff(day,[WorkForce_WorkOrder].[Work Order End Date_WO]
,[WorkForce_WorkOrder].[Previous Work Order End Date_WO] )
>= '0'
//
Since I'm new to TSQL world, wanted to know if my above code for my requirement is correct ? if not what should be the right code and why please?


Kind Regards
Dhananjay
Post #1428270
Posted Thursday, March 7, 2013 2:46 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 20,676, Visits: 32,269
If they are DATE or DATETIME data types why not just do this?

[WorkForce_WorkOrder].[Work Order End Date_WO] <= [WorkForce_WorkOrder].[Previous Work Order End Date_WO]



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1428274
Posted Thursday, March 7, 2013 2:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
I would prefer datediff since that way not dependent of type of date format in DB hence need help please
Post #1428275
Posted Thursday, March 7, 2013 2:57 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 20,676, Visits: 32,269
dhananjay.nagarkar (3/7/2013)
I would prefer datediff since that way not dependent of type of date format in DB hence need help please


If the columns are defined as one of the date data types, you don't need to use DATEDIFF. You can compare dates just like you can compare integers values.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1428279
Posted Thursday, March 7, 2013 3:05 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Hi Lynn,

that will not help since in my case one of the dates can remian NULL hence if that's the case then it will not work using > , = etc; hence datediff

thanks
Dhananjay
Post #1428283
Posted Thursday, March 7, 2013 3:16 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:39 AM
Points: 3,105, Visits: 11,494
dhananjay.nagarkar (3/7/2013)
Hi Lynn,

that will not help since in my case one of the dates can remian NULL hence if that's the case then it will not work using > , = etc; hence datediff

thanks
Dhananjay


If either if the date values is NULL, then it will fail the test either way, with or without DATEDIFF.

You need to explain what you want to happen if one or both of the values is NULL.



Post #1428288
Posted Thursday, March 7, 2013 3:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 20,676, Visits: 32,269
observe:


declare @TestDate datetime;
select datediff(dd,getdate(),@TestDate)
select datediff(dd,@TestDate,getdate())
set @TestDate = '20130318';
select datediff(dd,getdate(),@TestDate)
select datediff(dd,@TestDate,getdate())



-----------
NULL

(1 row(s) affected)


-----------
NULL

(1 row(s) affected)


-----------
11

(1 row(s) affected)


-----------
-11

(1 row(s) affected)





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1428293
Posted Thursday, March 7, 2013 6:21 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Thanks Lynn you are right
Post #1428341
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse