Solved

can SQL Server isdate be region specific?

Posted on 2007-11-28
12
875 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

19 Experts available now in Live!

Get 1:1 Help Now