January 8, 2013 at 3:21 am
Suppose I have a variable which contains date value in a string format.
eg: declare @value1 varchar(50) = '2013-01-08 15:44:12.2081606 +05:30'
Now my question is, how will i come to know whether the @value1 containing the date value as shown above is of which type? and based on the type it should return null, 1 or 0.
eg:
for datetime and datetimeoffset it should return 1,
for date type it should return 0,
and if @value1 doesn't contain any value then it should return NULL.
January 8, 2013 at 3:48 am
try using isDate() function.
or
try to convert the string using Convert function then check with isdate function.
January 8, 2013 at 3:50 am
If i use isdate() function also will not solve my issue.
my issue is to find out what is the datatype?
January 8, 2013 at 3:54 am
With difficulty
You could do a check in the string for the + or - symbol to indicate Offset
You could then do a check for a : to detail if a time has been put in the string for datetime
You could check the length of the variable is 10, if the date has been provided only
January 8, 2013 at 4:05 am
anthony.green (1/8/2013)
With difficultyYou could do a check in the string for the + or - symbol to indicate Offset
You could then do a check for a : to detail if a time has been put in the string for datetime
You could check the length of the variable is 10, if the date has been provided only
If user passes a string value with all these special characters will lead to wrong check.
eg: 'sdfsdfsfs:sdfsf', '2323232:2323:23:23 +2323'
January 8, 2013 at 4:09 am
You will need ISDATE(), LEN, CHARINDEX as a minumum to get this working.
But you should be doing your data validation in the front end, not within SQL.
January 8, 2013 at 6:16 pm
I agree with Anthony that this should be done in the front end. But you may want to examine the following results and see if this gives you some ideas.
WITH PotentialDates (d) AS (
SELECT '2013-01-08 15:44:12.2081606 +05:30'
UNION ALL SELECT '2013-01-08 15:44:12.2081606'
UNION ALL SELECT '2013-01-08 15:44:12.208'
UNION ALL SELECT '2013-01-08'
UNION ALL SELECT 'YYYY-DD-MM HH:MM:SS.mmmmmm'
)
SELECT IsDate=ISDATE(d)
,PATINDEX('[0-9][0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9]', LEFT(d, 10))
,ISDATE(LEFT(d, 23))
FROM PotentialDates
I found it interesting and somewhat unexpected that ISDATE() didn't work on the first two.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 9, 2013 at 1:27 am
ISDATE
Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
ISDATE returns 0 if the expression is a datetime2 value.
So as the first is a DateTimeOffset and the second is DateTime2, you would expect them to return 0.
DECLARE @String VARCHAR(50) = '2013-01-08 15:44:12.20848489 -05:30'
IF
PATINDEX('%[A-Z]%',@String) = 0
BEGIN
SELECT
CASE WHEN LEN(@String) = 10 THEN 'Date'
WHEN LEN(@String) = 23 THEN 'DateTime'
WHEN CHARINDEX('+',@String) > 0 THEN '+ Offset'
WHEN CHARINDEX('-',REVERSE(@String)) <=6 THEN '- Offset'
END AS WhatIsTheDataType
END
ELSE
SELECT 'You have not supplied a valid date'
January 9, 2013 at 3:00 am
anthony.green (1/9/2013)
ISDATE
Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
ISDATE returns 0 if the expression is a datetime2 value.
You are assuming sir that I read BOL. 😛 It is much more fun to experiment. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 9, 2013 at 3:01 am
dwain.c (1/9/2013)
anthony.green (1/9/2013)
ISDATE
Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
ISDATE returns 0 if the expression is a datetime2 value.
You are assuming sir that I read BOL. 😛 It is much more fun to experiment. 😀
Yep you are assuming that I read BOL too, I also fell into this trap when first looking at this yesterday.
January 9, 2013 at 12:58 pm
AS you've found out, the use of ISDATE is unreliable when doing implicit conversions.
Try these:
SELECT ISDATE('2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT ISDATE('2013-01-08 15:44:12.2081606') AS test
SELECT ISDATE('2013-01-08 15:44:12.208') AS test
The different results you get when trying to test a date's validity depends on the datatype.
Try these and see what happens:
SELECT CONVERT(SMALLDATETIME,'2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT CONVERT(DATETIME,'2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT CONVERT(DATETIME2,'2013-01-08 15:44:12.2081606 +05:30') AS test
SELECT CONVERT(DATETIMEOFFSET,'2013-01-08 15:44:12.2081606 +05:30') AS test
Here are two procedures that will test for valid dates. You can modify these to test for various datatypes as you are trying to do. These procedures avoid using the ISDATE function and simply attempt to convert what may or may not be a valid date into a valid datetime datatype. The first procedure tests based on the DATETIMEOFFSET datatype which is very forgiving and will return as a valid date something like '1234' as '1234-01-01 00:00:00.0000000 +00:00' which is in fact a valid date.
The second procedure tests based on the less forgiving SMALLDATETIME datatype, but also calls the first procedure if you want to check a date both ways. Of course, this second procedure can be modified to skip the call to the external procedure and the datatypes can be changed as well. But doing so may give different results!
I've also included a parameter in the second procedure so that the DATEFORMAT can be specified with a default of 'DMY'.
CREATE PROCEDURE [dbo].[IsValidDate]
@sDate VARCHAR(50)
,@sDateFormat CHAR(3) = 'DMY' -- MDY, DMY, YMD, YDM, MYD, DYM
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT @sDateFormat
DECLARE
@dStdDate SMALLDATETIME
,@dUMCDate DATETIMEOFFSET
,@bIsValidUMCDate BIT
,@bIsValidDate BIT
SET @dStdDate = NULL
SET @dUMCDate = NULL
/* This section is optional and could be left out */
/* Check to see if this is a valid UMC date */
IF OBJECT_ID('tempdb..#CheckUMCDate') IS NOT NULL
DROP TABLE #CheckUMCDate
CREATE TABLE #CheckUMCDate (
[ID] INT IDENTITY(1,1) NOT NULL,
[InputDate] VARCHAR(50) NULL,
[ConvertedDate] VARCHAR(50) NULL,
[IsValidUMCDate] BIT NULL
PRIMARY KEY (ID))
BEGIN TRY
INSERT INTO #CheckUMCDate
EXEC dbo.IsValidUMCDate @sDate
END TRY
BEGIN CATCH
INSERT INTO #CheckUMCDate
SELECT
@sDate AS InputDate
,NULL ConvertedDate
,0 AS IsValidUMCDate
END CATCH
SELECT
@dUMCDate = ConvertedDate
,@bIsValidUMCDate = IsValidUMCDate
FROM
#CheckUMCDate
/* End of optional section */
/* Check using regular SMALLDATETIME datatype */
BEGIN TRY
SET @dStdDate = CONVERT(SMALLDATETIME,@sDate)
SET @bIsValidDate = 1
END TRY
BEGIN CATCH
SET @bIsValidDate = 0
SET @dStdDate = NULL
END CATCH
SELECT
@sDate AS InputDate
,@dStdDate AS StdDate
,@bIsValidDate AS IsValidDate
,@dUMCDate AS UMCDate
,@bIsValidUMCDate AS IsValidUMCDate
/*
EXAMPLES:
EXEC dbo.IsValidDate '01-07-2001' -- Valid date
EXEC dbo.IsValidDate '1/7/2001' -- Valid date
EXEC dbo.IsValidDate '07-01-2001' -- Valid date
EXEC dbo.IsValidDate '7/1/2001' -- Valid date
EXEC dbo.IsValidDate '29-12-2013','DMY' -- Valid date in DMY format
EXEC dbo.IsValidDate '29-12-2013','MDY' -- Invalid date in MDY format
EXEC dbo.IsValidDate '32-12-2013' -- ERROR: Date out of range
EXEC dbo.IsValidDate '29-02-2013' -- ERROR: Not a leap year
EXEC dbo.IsValidDate 'xyz' -- ERROR: Invalid date
EXEC dbo.IsValidDate '1234' -- Invalid std date/valid umc date
EXEC dbo.IsValidDate '2013-01-08 15:44:12' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.000' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.208' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606' -- Invalid std date/valid umc date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30' -- Invalid std date/valid umc date
*/
END
GO
CREATE PROCEDURE [dbo].[IsValidUMCDate]
@sDate VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT DMY
DECLARE
@dUMCDate DATETIMEOFFSET
,@bIsValidUMCDate BIT
,@ERROR BIT
SET @dUMCDate = NULL
BEGIN TRY
SET @dUMCDate = CONVERT(DATETIMEOFFSET,@sDate)
SET @bIsValidUMCDate = 1
END TRY
BEGIN CATCH
SET @bIsValidUMCDate = 0
END CATCH
SET @ERROR = @bIsValidUMCDate
SELECT
@sDate AS InputDate
,@dUMCDate AS ConvertedDate
,@ERROR AS IsValidUMCDate
/*
EXAMPLES:
EXEC dbo.IsValidUMCDate '01-07-2001' -- Valid date
EXEC dbo.IsValidUMCDate '1/7/2001' -- Valid date
EXEC dbo.IsValidUMCDate '07-01-2001' -- Valid date
EXEC dbo.IsValidUMCDate '7/1/2001' -- Valid date
EXEC dbo.IsValidUMCDate '29-12-2013' -- Valid date
EXEC dbo.IsValidUMCDate '32-12-2013' -- ERROR: Date out of range
EXEC dbo.IsValidUMCDate '29-02-2013' -- ERROR: Not a leap year
EXEC dbo.IsValidUMCDate 'xyz' -- ERROR: Invalid date
EXEC dbo.IsValidUMCDate '1234' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.000' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.208' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.2081606' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.2081606 +05:30' -- Valid date
*/
END
GO
January 9, 2013 at 5:54 pm
Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 10, 2013 at 1:51 pm
dwain.c (1/9/2013)
Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis. 🙂
You might want to review the new functions in SQL 2012 dealing with conversion issues first. It might save you unneeded effort :-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 10, 2013 at 2:48 pm
ScottPletcher (1/10/2013)
dwain.c (1/9/2013)
Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis. 🙂You might want to review the new functions in SQL 2012 dealing with conversion issues first. It might save you unneeded effort :-).
Thanks Scott...I've just recently gotten my hands on a SQL2012 instance. I deal with over 100 different database servers and I just got my first client using SQL2012. It will be awhile before I can jump too far ahead. I'm still unable to use MERGE and a number of other 2008 additions for some of my clients because they are still on SQL2005. :angry:
Fortunately, I don't have to deal with any backward compatibility issues prior to that! Perhaps you could write an article on the new SQL2012 date functions? A quick search only showed one SQL Spackle Article on performance of 2012 date functions.
January 10, 2013 at 2:57 pm
Steven Willis (1/10/2013)
ScottPletcher (1/10/2013)
dwain.c (1/9/2013)
Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis. 🙂You might want to review the new functions in SQL 2012 dealing with conversion issues first. It might save you unneeded effort :-).
Thanks Scott...I've just recently gotten my hands on a SQL2012 instance. I deal with over 100 different database servers and I just got my first client using SQL2012. It will be awhile before I can jump too far ahead. I'm still unable to use MERGE and a number of other 2008 additions for some of my clients because they are still on SQL2005. :angry:
Fortunately, I don't have to deal with any backward compatibility issues prior to that! Perhaps you could write an article on the new SQL2012 date functions? A quick search only showed one SQL Spackle Article on performance of 2012 date functions.
In this case, I was thinking of the PARSE, TRY_PARSE and TRY_CONVERT functions. They can do the data type checking for you in SQL 2012.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply