InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 05:23:28 GMT20http://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspxdeclare@datetime datetimeoffset = '2012-10-25 00:00:00.000 +04:30' selectcasewhen (DATEPART(TZ, @datetime) <> 0 and DATEPART(TZ, @datetime) is not null OR CONVERT(TIME(0),@datetime) <> '00:00:00')then 0when (DATEPART(TZ, @datetime) = 0 and @datetime <> '')then 1when (DATEPART(TZ, @datetime) is null)then NULLend as PrecisionFri, 11 Jan 2013 03:24:54 GMTvivekkumar341http://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspx[quote][b]ScottPletcher (1/10/2013)[/b][hr][quote][b]dwain.c (1/9/2013)[/b][hr]Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis. :-)[/quote]You might want to review the new functions in SQL 2012 dealing with conversion issues first. It might save you unneeded effort :-).[/quote]Noted for future reference because, alas, at this time I must work only with the stone knives and bear skins available in SQL 2008.Thu, 10 Jan 2013 17:32:42 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspx[quote][b]Steven Willis (1/10/2013)[/b][hr][quote][b]ScottPletcher (1/10/2013)[/b][hr][quote][b]dwain.c (1/9/2013)[/b][hr]Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis. :-)[/quote]You might want to review the new functions in SQL 2012 dealing with conversion issues first. It might save you unneeded effort :-).[/quote]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. [/quote]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.Thu, 10 Jan 2013 14:57:12 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspx[quote][b]ScottPletcher (1/10/2013)[/b][hr][quote][b]dwain.c (1/9/2013)[/b][hr]Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis. :-)[/quote]You might want to review the new functions in SQL 2012 dealing with conversion issues first. It might save you unneeded effort :-).[/quote]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. Thu, 10 Jan 2013 14:48:03 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspx[quote][b]dwain.c (1/9/2013)[/b][hr]Steven - Those FUNCTIONs look like nice work. I've saved them off for future analysis. :-)[/quote]You might want to review the new functions in SQL 2012 dealing with conversion issues first. It might save you unneeded effort :-).Thu, 10 Jan 2013 13:51:27 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspxSteven - Those FUNCTIONs look like nice work. I've saved them off for future analysis. :-)Wed, 09 Jan 2013 17:54:56 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspxAS you've found out, the use of ISDATE is unreliable when doing implicit conversions.Try these:[code="sql"]SELECT ISDATE('2013-01-08 15:44:12.2081606 +05:30') AS testSELECT ISDATE('2013-01-08 15:44:12.2081606') AS testSELECT ISDATE('2013-01-08 15:44:12.208') AS test[/code]The different results you get when trying to test a date's validity depends on the datatype.Try these and see what happens:[code="sql"]SELECT CONVERT(SMALLDATETIME,'2013-01-08 15:44:12.2081606 +05:30') AS testSELECT CONVERT(DATETIME,'2013-01-08 15:44:12.2081606 +05:30') AS testSELECT CONVERT(DATETIME2,'2013-01-08 15:44:12.2081606 +05:30') AS testSELECT CONVERT(DATETIMEOFFSET,'2013-01-08 15:44:12.2081606 +05:30') AS test[/code]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'.[code="sql"]CREATE PROCEDURE [dbo].[IsValidDate] @sDate VARCHAR(50) ,@sDateFormat CHAR(3) = 'DMY' -- MDY, DMY, YMD, YDM, MYD, DYMASBEGIN 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 */ENDGO[/code][code="sql"]CREATE PROCEDURE [dbo].[IsValidUMCDate] @sDate VARCHAR(50)ASBEGIN 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 */ENDGO[/code]Wed, 09 Jan 2013 12:58:58 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspx[quote][b]dwain.c (1/9/2013)[/b][hr][quote][b]anthony.green (1/9/2013)[/b][hr][quote]ISDATEReturns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.ISDATE returns 0 if the expression is a datetime2 value.[/quote][/quote]You are assuming sir that I read BOL. :-P It is much more fun to experiment. :-D[/quote]Yep you are assuming that I read BOL too, I also fell into this trap when first looking at this yesterday.Wed, 09 Jan 2013 03:01:39 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspx[quote][b]anthony.green (1/9/2013)[/b][hr][quote]ISDATEReturns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.ISDATE returns 0 if the expression is a datetime2 value.[/quote][/quote]You are assuming sir that I read BOL. :-P It is much more fun to experiment. :-DWed, 09 Jan 2013 03:00:15 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspx[quote]ISDATEReturns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.ISDATE returns 0 if the expression is a datetime2 value.[/quote]So as the first is a DateTimeOffset and the second is DateTime2, you would expect them to return 0.[code="sql"]DECLARE @String VARCHAR(50) = '2013-01-08 15:44:12.20848489 -05:30'IF PATINDEX('%[A-Z]%',@String) = 0BEGIN 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 ENDELSESELECT 'You have not supplied a valid date'[/code]Wed, 09 Jan 2013 01:27:13 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspxI 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.[code="sql"]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[/code]I found it interesting and somewhat unexpected that ISDATE() didn't work on the first two.Tue, 08 Jan 2013 18:16:17 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspxYou 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.Tue, 08 Jan 2013 04:09:42 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspx[quote][b]anthony.green (1/8/2013)[/b][hr]With difficultyYou could do a check in the string for the + or - symbol to indicate OffsetYou could then do a check for a : to detail if a time has been put in the string for datetimeYou could check the length of the variable is 10, if the date has been provided only[/quote]If user passes a string value with all these special characters will lead to wrong check.eg: 'sdfsdfsfs:sdfsf', '2323232:2323:23:23 +2323'Tue, 08 Jan 2013 04:05:37 GMTvivekkumar341http://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspxWith difficultyYou could do a check in the string for the + or - symbol to indicate OffsetYou could then do a check for a : to detail if a time has been put in the string for datetimeYou could check the length of the variable is 10, if the date has been provided onlyTue, 08 Jan 2013 03:54:50 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspxIf i use isdate() function also will not solve my issue.my issue is to find out what is the datatype?Tue, 08 Jan 2013 03:50:46 GMTvivekkumar341http://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspxtry using isDate() function.ortry to convert the string using Convert function then check with isdate function.Tue, 08 Jan 2013 03:48:58 GMTPrassad Dabbada V Rhttp://www.sqlservercentral.com/Forums/Topic1404092-392-1.aspxSuppose 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.Tue, 08 Jan 2013 03:21:18 GMTvivekkumar341