?
Solved

can SQL Server isdate be region specific?

Posted on 2007-11-28
12
Medium Priority
?
882 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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
 

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 1400 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

There are some very powerful Dynamic 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 di…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

801 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