• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 887
  • Last Modified:

can SQL Server isdate be region specific?

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
StevenLogic
Asked:
StevenLogic
  • 8
  • 4
1 Solution
 
YveauCommented:
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
 
StevenLogicAuthor Commented:
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
 
YveauCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
StevenLogicAuthor Commented:
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
 
StevenLogicAuthor Commented:
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
 
YveauCommented:
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
 
StevenLogicAuthor Commented:
Hey!   That looks good.  I will give it a 'try' tomorrow.  Thanks for the help.
0
 
StevenLogicAuthor Commented:
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
 
StevenLogicAuthor Commented:
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
 
YveauCommented:
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
 
StevenLogicAuthor Commented:
drat
0
 
StevenLogicAuthor Commented:
thanks though
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now