January 23, 2013 at 8:22 am
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
January 23, 2013 at 8:38 am
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 [/url]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
January 23, 2013 at 8:48 am
Thanks for the reply. Got it.
January 23, 2013 at 10:52 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 24, 2013 at 9:08 am
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 [/url]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
January 24, 2013 at 12:41 pm
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
January 24, 2013 at 2:39 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 24, 2013 at 4:10 pm
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?
January 24, 2013 at 4:31 pm
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.
January 24, 2013 at 4:48 pm
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
January 24, 2013 at 4:50 pm
Of course if you just code your dates as 'YYYYMMDD', conversion always works, and you don't have to waste a second on any of that other garbage.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 24, 2013 at 4:54 pm
That's good to know. But my point was concerning the ISDATE function. As these examples point out, no matter how sound the logic may be for a CAST (or CONVERT) of a specific date datatype, or no matter how well the inner-workings are explained in BOL, it's seems to me that using ISDATE is a time bomb.
I used to routinely use ISDATE until I read several articles and posts here on SQLServerCentral and elsewhere concerning it's potential pitfalls. I did the prudent thing and did a lot of testing and discovered the inconsistencies I pointed out above. I'm not saying the datatypes themselves are at issue, just that ISDATE doesn't seem to have been updated as have the date datatypes themselves (at least through SQL2008R2).
When inserting/updating a potential date the thing that matters is the date datatype of the destination column. Just because ISDATE says a value is a "date" doesn't mean the column is going to accept it.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply