Solved

can SQL Server isdate be region specific?

Posted on 2007-11-28
12
876 Views
Last Modified: 2008-02-01
Hi,

I would like to test if a column conforms to a particular format type, and then convert it only if all is okay.

select case when isdate([COL4])  = 1   then convert(datetime, [COL4], 112)    else NULL   END      AS field1
from importStaging

In addition, select ISDATE('21/11/07') returns 0 and select ISDATE('11/27/07') returns 1.

obviously server is set to US format... but I would like to be able to select from text + convert when possible + not fail for a large select statement.

any help appreciated.

-Steven
0
Comment
Question by:StevenLogic
  • 8
  • 4
12 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20365086
It looks like the convert you used is wrong, omit the 112 and it seems to work:

Hope this helps ...

create table #Yveau (ID int,col1 varchar(8))

go
 

insert into #Yveau values (1,'21/11/07')

insert into #Yveau values (2,'11/21/07')

go
 

select  ID, case

        when isdate(col1) = 1 then convert(datetime, col1)

        else NULL

        end

from    #Yveau
 

-->> Result:

ID          

----------- -----------------------

1           NULL

2           2007-11-21 00:00:00.000

Open in new window

0
 

Author Comment

by:StevenLogic
ID: 20365385
Thanks for looking.

The question is more to do with there seems to be no way to test a specific date format without raising an error.  For instance, writing a function, trying to conver using 103, and then return 1 if @@error = 0 would do it.  

I was hoping there was a native SQL Server command to do this using the current structure.

Staying with the current example

select isdate('11/21/2007')
returns: 1

select isdate('21/11/2007')
returns: error

Select convert(datetime, '21/11/2007', 103)
returns: 2007-11-21 00:00:00.000
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20365451
Noop, not native. You could write your own ...

And the pain with the 'set dateformat()' function is that you can't run that in a procedure.
That setting will affect the behaviour of the IsDate() function.

Hope this helps ...
0
 

Author Comment

by:StevenLogic
ID: 20365607
I'm going to leave this open for a few days + give points to Yveau unless someone comes up with a native solution.

In the meantime please post any improvements your can think of to make the below more efficient.  No points for this though as my original question was quite specific.

Thanks


.......................................

DROP FUNCTION fIsDate;

CREATE FUNCTION fIsDate(@i_dateStr VARCHAR(20), @i_dateConvType INT)
RETURNS  INT
AS
BEGIN
      DECLARE @DTE AS DATETIME
      DECLARE @RET AS INT

      SELECT @DTE = CONVERT(DATETIME, @i_dateStr, @i_dateConvType);
      
      IF @@ERROR = 0
            SELECT @RET = 1
      ELSE
            SELECT @RET = 0
      RETURN   @RET;
END

.......................................
0
 

Author Comment

by:StevenLogic
ID: 20365854
actually, maybe there should be points for this, as the above does not work

select dbo.fIsDate('11/22/2007', 103)
returns: error
select dbo.fIsDate('22/11/2007', 103)
returns: 1

is there a way to suppress the error from killing the function?

thanks
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20366627
This construction will result november 28th in all three cases:
I don't know whether this might be acceptable for you ?

Hope this helps ...

declare @res datetime

declare @test varchar(20)

-- set     @test = '20071128'     -- case #1

-- set     @test = '11/28/2007'   -- case #2

-- set     @test = '28/11/2007'   -- case #3
 

begin try

        select  @res = CONVERT(DATETIME, @test, 101)

end try

begin catch

        select  @res = CONVERT(DATETIME, @test, 103)

end catch
 

select  @res
 

--

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:StevenLogic
ID: 20390798
Hey!   That looks good.  I will give it a 'try' tomorrow.  Thanks for the help.
0
 

Author Comment

by:StevenLogic
ID: 20394404
It is comming back to me.  I did try a solution like this, but when I placed this kind of logic in a function I get:
"Msg 443, Level 16, State 14, Procedure fIsDate, Line 11
Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function."



CREATE FUNCTION fIsDate(@i_dateStr VARCHAR(20), @i_dateConvType INT)

RETURNS  INT

AS

BEGIN
 

	DECLARE @ret DATETIME

	 

	BEGIN TRY

			SELECT  @ret = CONVERT(DATETIME, @i_dateStr, @i_dateConvType)

	END TRY

	BEGIN CATCH

		SELECT  @ret = NULL

	END CATCH
 

    RETURN   @RET;

END

Open in new window

0
 

Author Comment

by:StevenLogic
ID: 20394421
To be more accurate.  Appologies on incorrect function (anyone know how to delete my previous post)

I did try a solution like this, but when I placed this kind of logic in a function I get:
"Msg 443, Level 16, State 14, Procedure fIsDate, Line 11
Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function."


CREATE FUNCTION fIsDate(@i_dateStr VARCHAR(20), @i_dateConvType INT)

RETURNS  INT

AS

BEGIN
 

	DECLARE @test datetime

	DECLARE @ret int

	 

	BEGIN TRY

			SELECT  @test = CONVERT(DATETIME, @i_dateStr, @i_dateConvType)

			SELECT @ret = 1

	END TRY

	BEGIN CATCH

		SELECT  @ret = 1

	END CATCH
 

    RETURN   @RET;

END

Open in new window

0
 
LVL 18

Accepted Solution

by:
Yveau earned 350 total points
ID: 20404975
I can't find the actual words in the books online, but it looks like it that try - catch constructs can't be used in a function.

Hope this helps ...
0
 

Author Comment

by:StevenLogic
ID: 20721614
drat
0
 

Author Comment

by:StevenLogic
ID: 20721616
thanks though
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now