Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

can SQL Server isdate be region specific?

Posted on 2007-11-28
12
Medium Priority
?
883 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

609 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