Solved

can SQL Server isdate be region specific?

Posted on 2007-11-28
12
878 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to extract a "coded date" from a string field? 4 64
Insert statement is inserting duplicate records 15 62
SQL query with cast 38 50
Email Notifications for SQL 2005 9 35
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…
In this article I will describe the Backup & Restore 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.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

766 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