Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Subtracting specific dates from today
Add to briefcase
12 posts, Page 1 of 2
12»»

Subtracting specific dates from today Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
mbrady5
Posted Wednesday, January 23, 2013 8:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:14 AM
Points: 85, Visits: 403
Hello,

I know this isn't too difficult, but I am have a momentary brain lapse.

How can I calculate the number of days between Today and a specific date in a data set?

Thanks
Post #1410614
Stuart Davies
Posted Wednesday, January 23, 2013 8:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:19 AM
Points: 2,473, Visits: 2,131
try DATEDIFF ( datepart , startdate , enddate )

SELECT DATEDIFF (d,GETDATE(), '31 dec 2013') for example gives the result of 342


(Not to OP)

The first parameter (for completeness for usage of datepart) can be various values see BOL for more info

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1410627
mbrady5
Posted Wednesday, January 23, 2013 8:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:14 AM
Points: 85, Visits: 403
Thanks for the reply. Got it.
Post #1410637
ScottPletcher
Posted Wednesday, January 23, 2013 10:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 1,318, Visits: 1,763
Btw, I suggest using the clear datepart name, not the obscure abbreviations -- it can help you/others later, and avoid having to use Books Online just to lookup the &#^)@ abbreviation:

DATEADD(DAY, ... vs DATEADD(d, ...)
DATEADD(MINUTE, ... vs DATEADD(n, ...)

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1410710
Stuart Davies
Posted Thursday, January 24, 2013 9:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:19 AM
Points: 2,473, Visits: 2,131
ScottPletcher (1/23/2013)
Btw, I suggest using the clear datepart name, not the obscure abbreviations -- it can help you/others later, and avoid having to use Books Online just to lookup the &#^)@ abbreviation:

DATEADD(DAY, ... vs DATEADD(d, ...)
DATEADD(MINUTE, ... vs DATEADD(n, ...)

Agreed !

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1411196
Steven Willis
Posted Thursday, January 24, 2013 12:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:23 AM
Points: 283, Visits: 1,237
To prevent unexpected errors, you should validate your dates. And the ISDATE function is not a reliable method for such validation.

DECLARE 
	 @InputDate DATETIME
	,@CurrDate DATETIME

SET @InputDate = '1/1/2013'
SET @CurrDate = GETDATE()

--so far so good
SELECT DATEDIFF(DAY,@InputDate,@CurrDate) AS DayDiff

SET @InputDate = '1/32/2013'  --an invalid date
SET @CurrDate = GETDATE()

--ooops...The conversion of a varchar data type 
--to a datetime data type resulted in an out-of-range value.
SELECT DATEDIFF(DAY,@InputDate,@CurrDate) AS DayDiff

So you should validate by trying to convert the date from a VARCHAR to DATETIME first. Some examples with code for a simple procedure below.

DECLARE 
	 @InputDate1 VARCHAR(20) -- yes, input the date as VARCHAR!
	,@CurrDate1 DATETIME
	,@ERROR VARCHAR(50) 

SET @CurrDate1 = GETDATE()
SET @InputDate1 = '1/30/2013'       --this is a valid date
--SET @InputDate1 = '1/32/2013'    --swap this one in and see what happens

BEGIN TRY
	EXEC [dbo].[DateValidation] @InputDate1, @ERROR = @ERROR OUTPUT	
	IF @ERROR <> 'INVALID DATE'
		SELECT DATEDIFF(DAY,@InputDate1,@CurrDate1) AS DayDiff
END TRY
BEGIN CATCH
	SELECT 'INVALID DATE' AS DateStatus
END CATCH


CREATE PROCEDURE [dbo].[DateValidation]

     @dInputDate VARCHAR(20)
    ,@ERROR VARCHAR(50) OUTPUT

AS
BEGIN

    /*
    Examples:

    DECLARE @ERROR VARCHAR(50) 
    EXEC [dbo].[DateValidation] '01-07-2001', @ERROR = @ERROR OUTPUT    -- Valid date
    EXEC [dbo].[DateValidation] '32-12-2013', @ERROR = @ERROR OUTPUT    -- ERROR: Date out of range
    EXEC [dbo].[DateValidation] 'xyz', @ERROR = @ERROR OUTPUT        -- ERROR: Invalid date

    */
    
    SET NOCOUNT ON

    BEGIN TRY
       SET @dInputDate = CAST(@dInputDate AS SMALLDATETIME)
    END TRY
    BEGIN CATCH
        SET @ERROR = 'INVALID DATE'
        SELECT @ERROR AS BadDate
        RETURN
    END CATCH

    SELECT 
         @dInputDate AS GoodStartDate

END
GO
Post #1411303
ScottPletcher
Posted Thursday, January 24, 2013 2:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 1,318, Visits: 1,763
Steven Willis (1/24/2013)To prevent unexpected errors, you should validate your dates. And the ISDATE function is not a reliable method for such validation.



ISDATE() is reliable for what it claims to check: date, time and datetime.

You just have to use it properly to avoid comparing/converting an invalid date value to a date / time / datetime data type. In particular, you have to keep in mind the potential non-linear order of SQL execution.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1411345
Steven Willis
Posted Thursday, January 24, 2013 4:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:23 AM
Points: 283, Visits: 1,237
ISDATE isn't quite in sync with all of the date datatypes. I just think it's prudent to prevent an unexpected failure of a procedure to check an alleged "date" by trying to CAST it as the date datatype needed. If that succeeds at least the procedure won't break or cause an insert/update to fail. In my example I was validating against SMALLDATETIME, but that could (and should) be changed to validate based on the datatype being used to process and store the dates.


SELECT ISDATE('999')                 --> false
SELECT CAST('999' AS DATE)           --> error
SELECT CAST('999' AS SMALLDATETIME)  --> error
SELECT CAST('999' AS DATETIME)       --> error
SELECT CAST('999' AS DATETIME2)      --> error
SELECT CAST('999' AS DATETIMEOFFSET) --> error

SELECT ISDATE('1000')                 --> false
SELECT CAST('1000' AS DATE)           --> ok
SELECT CAST('1000' AS SMALLDATETIME)  --> error
SELECT CAST('1000' AS DATETIME)       --> error
SELECT CAST('1000' AS DATETIME2)      --> ok
SELECT CAST('1000' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('2000')                 --> true
SELECT CAST('2000' AS DATE)           --> ok
SELECT CAST('2000' AS SMALLDATETIME)  --> ok
SELECT CAST('2000' AS DATETIME)       --> ok
SELECT CAST('2000' AS DATETIME2)      --> ok
SELECT CAST('2000' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1/1/999')                 --> false
SELECT CAST('1/1/999' AS DATE)           --> error
SELECT CAST('1/1/999' AS SMALLDATETIME)  --> error
SELECT CAST('1/1/999' AS DATETIME)       --> error
SELECT CAST('1/1/999' AS DATETIME2)      --> error
SELECT CAST('1/1/999' AS DATETIMEOFFSET) --> error

SELECT ISDATE('1/1/1000')                 --> false
SELECT CAST('1/1/1000' AS DATE)           --> error
SELECT CAST('1/1/1000' AS SMALLDATETIME)  --> error
SELECT CAST('1/1/1000' AS DATETIME)       --> error
SELECT CAST('1/1/1000' AS DATETIME2)      --> ok
SELECT CAST('1/1/1000' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1/1/1752')                 --> false
SELECT CAST('1/1/1752' AS DATE)           --> error
SELECT CAST('1/1/1752' AS SMALLDATETIME)  --> error
SELECT CAST('1/1/1752' AS DATETIME)       --> error
SELECT CAST('1/1/1752' AS DATETIME2)      --> ok
SELECT CAST('1/1/1752' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1/1/1753')                 --> true
SELECT CAST('1/1/1753' AS DATE)           --> error
SELECT CAST('1/1/1753' AS SMALLDATETIME)  --> error
SELECT CAST('1/1/1753' AS DATETIME)       --> ok
SELECT CAST('1/1/1753' AS DATETIME2)      --> ok
SELECT CAST('1/1/1753' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1/1/1899')                 --> true
SELECT CAST('1/1/1899' AS DATE)           --> error
SELECT CAST('1/1/1899' AS SMALLDATETIME)  --> error
SELECT CAST('1/1/1899' AS DATETIME)       --> ok
SELECT CAST('1/1/1899' AS DATETIME2)      --> ok
SELECT CAST('1/1/1899' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1/1/1900')                 --> true
SELECT CAST('1/1/1900' AS DATE)           --> error
SELECT CAST('1/1/1900' AS SMALLDATETIME)  --> ok
SELECT CAST('1/1/1900' AS DATETIME)       --> ok
SELECT CAST('1/1/1900' AS DATETIME2)      --> ok
SELECT CAST('1/1/1900' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1899-01-01')                 --> true
SELECT CAST('1899-01-01' AS DATE)           --> ok
SELECT CAST('1899-01-01' AS SMALLDATETIME)  --> error
SELECT CAST('1899-01-01' AS DATETIME)       --> ok
SELECT CAST('1899-01-01' AS DATETIME2)      --> ok
SELECT CAST('1899-01-01' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1900-01-01')                 --> true
SELECT CAST('1900-01-01' AS DATE)           --> ok
SELECT CAST('1900-01-01' AS SMALLDATETIME)  --> ok
SELECT CAST('1900-01-01' AS DATETIME)       --> ok
SELECT CAST('1900-01-01' AS DATETIME2)      --> ok
SELECT CAST('1900-01-01' AS DATETIMEOFFSET) --> ok


And setting a specific DATEFORMAT can change the result, but not predictably...


SET DATEFORMAT YMD  --> all these make sense
SELECT ISDATE('2012-12-25')                 --> true
SELECT CAST('2012-12-25' AS DATE)           --> ok
SELECT CAST('2012-12-25' AS SMALLDATETIME)  --> ok
SELECT CAST('2012-12-25' AS DATETIME)       --> ok
SELECT CAST('2012-12-25' AS DATETIME2)      --> ok
SELECT CAST('2012-12-25' AS DATETIMEOFFSET) --> ok

SET DATEFORMAT YDM  --> but wtf?
SELECT ISDATE('2012-12-25')                 --> false
SELECT CAST('2012-12-25' AS DATE)           --> ok?
SELECT CAST('2012-12-25' AS SMALLDATETIME)  --> error
SELECT CAST('2012-12-25' AS DATETIME)       --> error
SELECT CAST('2012-12-25' AS DATETIME2)      --> ok?
SELECT CAST('2012-12-25' AS DATETIMEOFFSET) --> ok?



 
Post #1411389
Lynn Pettis
Posted Thursday, January 24, 2013 4:31 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 21,588, Visits: 27,385
Steven Willis (1/24/2013)
ISDATE isn't quite in sync with all of the date datatypes. I just think it's prudent to prevent an unexpected failure of a procedure to check an alleged "date" by trying to CAST it as the date datatype needed. If that succeeds at least the procedure won't break or cause an insert/update to fail. In my example I was validating against SMALLDATETIME, but that could (and should) be changed to validate based on the datatype being used to process and store the dates.


SELECT ISDATE('999')                 --> false
SELECT CAST('999' AS DATE)           --> error
SELECT CAST('999' AS SMALLDATETIME)  --> error
SELECT CAST('999' AS DATETIME)       --> error
SELECT CAST('999' AS DATETIME2)      --> error
SELECT CAST('999' AS DATETIMEOFFSET) --> error

SELECT ISDATE('1000')                 --> false
SELECT CAST('1000' AS DATE)           --> ok
SELECT CAST('1000' AS SMALLDATETIME)  --> error
SELECT CAST('1000' AS DATETIME)       --> error
SELECT CAST('1000' AS DATETIME2)      --> ok
SELECT CAST('1000' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('2000')                 --> true
SELECT CAST('2000' AS DATE)           --> ok
SELECT CAST('2000' AS SMALLDATETIME)  --> ok
SELECT CAST('2000' AS DATETIME)       --> ok
SELECT CAST('2000' AS DATETIME2)      --> ok
SELECT CAST('2000' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1/1/999')                 --> false
SELECT CAST('1/1/999' AS DATE)           --> error
SELECT CAST('1/1/999' AS SMALLDATETIME)  --> error
SELECT CAST('1/1/999' AS DATETIME)       --> error
SELECT CAST('1/1/999' AS DATETIME2)      --> error
SELECT CAST('1/1/999' AS DATETIMEOFFSET) --> error

SELECT ISDATE('1/1/1000')                 --> false
SELECT CAST('1/1/1000' AS DATE)           --> error
SELECT CAST('1/1/1000' AS SMALLDATETIME)  --> error
SELECT CAST('1/1/1000' AS DATETIME)       --> error
SELECT CAST('1/1/1000' AS DATETIME2)      --> ok
SELECT CAST('1/1/1000' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1/1/1752')                 --> false
SELECT CAST('1/1/1752' AS DATE)           --> error
SELECT CAST('1/1/1752' AS SMALLDATETIME)  --> error
SELECT CAST('1/1/1752' AS DATETIME)       --> error
SELECT CAST('1/1/1752' AS DATETIME2)      --> ok
SELECT CAST('1/1/1752' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1/1/1753')                 --> true
SELECT CAST('1/1/1753' AS DATE)           --> error
SELECT CAST('1/1/1753' AS SMALLDATETIME)  --> error
SELECT CAST('1/1/1753' AS DATETIME)       --> ok
SELECT CAST('1/1/1753' AS DATETIME2)      --> ok
SELECT CAST('1/1/1753' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1/1/1899')                 --> true
SELECT CAST('1/1/1899' AS DATE)           --> error
SELECT CAST('1/1/1899' AS SMALLDATETIME)  --> error
SELECT CAST('1/1/1899' AS DATETIME)       --> ok
SELECT CAST('1/1/1899' AS DATETIME2)      --> ok
SELECT CAST('1/1/1899' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1/1/1900')                 --> true
SELECT CAST('1/1/1900' AS DATE)           --> error
SELECT CAST('1/1/1900' AS SMALLDATETIME)  --> ok
SELECT CAST('1/1/1900' AS DATETIME)       --> ok
SELECT CAST('1/1/1900' AS DATETIME2)      --> ok
SELECT CAST('1/1/1900' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1899-01-01')                 --> true
SELECT CAST('1899-01-01' AS DATE)           --> ok
SELECT CAST('1899-01-01' AS SMALLDATETIME)  --> error
SELECT CAST('1899-01-01' AS DATETIME)       --> ok
SELECT CAST('1899-01-01' AS DATETIME2)      --> ok
SELECT CAST('1899-01-01' AS DATETIMEOFFSET) --> ok

SELECT ISDATE('1900-01-01')                 --> true
SELECT CAST('1900-01-01' AS DATE)           --> ok
SELECT CAST('1900-01-01' AS SMALLDATETIME)  --> ok
SELECT CAST('1900-01-01' AS DATETIME)       --> ok
SELECT CAST('1900-01-01' AS DATETIME2)      --> ok
SELECT CAST('1900-01-01' AS DATETIMEOFFSET) --> ok


And setting a specific DATEFORMAT can change the result, but not predictably...


SET DATEFORMAT YMD  --> all these make sense
SELECT ISDATE('2012-12-25')                 --> true
SELECT CAST('2012-12-25' AS DATE)           --> ok
SELECT CAST('2012-12-25' AS SMALLDATETIME)  --> ok
SELECT CAST('2012-12-25' AS DATETIME)       --> ok
SELECT CAST('2012-12-25' AS DATETIME2)      --> ok
SELECT CAST('2012-12-25' AS DATETIMEOFFSET) --> ok

SET DATEFORMAT YDM  --> but wtf?
SELECT ISDATE('2012-12-25')                 --> false
SELECT CAST('2012-12-25' AS DATE)           --> ok?
SELECT CAST('2012-12-25' AS SMALLDATETIME)  --> error
SELECT CAST('2012-12-25' AS DATETIME)       --> error
SELECT CAST('2012-12-25' AS DATETIME2)      --> ok?
SELECT CAST('2012-12-25' AS DATETIMEOFFSET) --> ok?



 


I will answer your question regarding why the CAST of '2012-12-25' worked for the DATE, DATETIME2, and DATETIMEOFFSET data types. These three data types are defined to have the default string format for date representations to be YYYY-MM-DD. Absent the formatting information used in the CONVERT statement, SQL was able to correctly convert the string values in your CAST functions to a valid date.

You will find this information in Books Online where it talks about each of these date/time data types.


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 #1411397
Lynn Pettis
Posted Thursday, January 24, 2013 4:48 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 21,588, Visits: 27,385
FYI:


SET DATEFORMAT YDM
SELECT ISDATE('2012-25-12')                 --> true
SELECT CAST('2012-25-12' AS DATE)           --> error
SELECT CAST('2012-25-12' AS SMALLDATETIME)  --> ok
SELECT CAST('2012-25-12' AS DATETIME)       --> ok
SELECT CAST('2012-25-12' AS DATETIME2)      --> error
SELECT CAST('2012-25-12' AS DATETIMEOFFSET) --> error


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 #1411406
« Prev Topic | Next Topic »

Add to briefcase
12 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.