check if string is a valid Time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

check if string is a valid Time

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-25 : 10:52:49

Hi,

How can I check a piece of string(VARCHAR(15) if it is a valid time or not.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 10:54:36

how will be format of values stored in it?

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-25 : 10:56:54

quote:
Originally posted by visakh16

how will be format of values stored in it?

It would be 00:00:00 which will later be combined with the datetime column to make it like this: 07/12/2007 11:23:05

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-25 : 11:36:29

one validation I have worked on checks the format of the string.


 --**************************************************
 SELECT @booValid=0
 IF LEN (@Start_Time) = 8
  IF	(ISNUMERIC(SUBSTRING(@Start_Time,1,2)) = 1) AND 
		(ISNUMERIC(SUBSTRING(@Start_Time,4,2)) = 1) AND
		(ISNUMERIC(SUBSTRING(@Start_Time,7,2)) = 1) 
    SELECT @booValid=1
 IF @booValid=0
  BEGIN
   SELECT @Error_Codes = @Error_Codes + 8
   SELECT @Error_Descriptions = @Error_Descriptions + 'The start time is not in the format hh:mm:ss'+ CHAR(13) + CHAR(11)
   SELECT @booValid=1
  END
 --**************************************************

are there any other validations which i can apply for time string?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-25 : 11:45:36

Maybe u can simplify ur query like this..

--**************************************************
SELECT @booValid=0
IF LEN (@Start_Time) = 8
IF ISNUMERIC(REPLACE(@Start_Time,':','')) = 1
SELECT @booValid=1
IF @booValid=0
BEGIN
SELECT @Error_Codes = @Error_Codes + 8
SELECT @Error_Descriptions = @Error_Descriptions + 'The start time is not in the format hh:mm:ss'+ CHAR(13) + CHAR(11)
SELECT @booValid=1
END
--**************************************************

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-25 : 12:03:27

That one does not work properly:


DECLARE @Time varchar(15)    
SET @Time = '00:0e:00'

IF LEN (@Time) = 8
	BEGIN
	if ISNUMERIC(REPLACE(@Time,':','')) = 1 
--	  IF	(ISNUMERIC(SUBSTRING(@Time,1,2)) = 1) AND 
--			(ISNUMERIC(SUBSTRING(@Time,4,2)) = 1) AND
--			(ISNUMERIC(SUBSTRING(@Time,7,2)) = 1) 
			PRINT 'All numbers are valid integers'
		ELSE
			PRINT 'All numbers are not valid integers'
	END
	
ELSE PRINT 'Length of the time string is too long'

Result:
All numbers are valid integers

However, the orignal one does pick up on the alphabet


DECLARE @Time varchar(15)    
SET @Time = '00:0e:00'

IF LEN (@Time) = 8
	BEGIN
	  IF	(ISNUMERIC(SUBSTRING(@Time,1,2)) = 1) AND 
			(ISNUMERIC(SUBSTRING(@Time,4,2)) = 1) AND
			(ISNUMERIC(SUBSTRING(@Time,7,2)) = 1) 
			PRINT 'All numbers are valid integers'
		ELSE
			PRINT 'All numbers are not valid integers'
	END
	
ELSE PRINT 'Length of the time string is too long'

Result:
All numbers are not valid integers

quote:
Originally posted by vijayisonly

Maybe u can simplify ur query like this..

--**************************************************
SELECT @booValid=0
IF LEN (@Start_Time) = 8
IF ISNUMERIC(REPLACE(@Start_Time,':','')) = 1
SELECT @booValid=1
IF @booValid=0
BEGIN
SELECT @Error_Codes = @Error_Codes + 8
SELECT @Error_Descriptions = @Error_Descriptions + 'The start time is not in the format hh:mm:ss'+ CHAR(13) + CHAR(11)
SELECT @booValid=1
END
--**************************************************

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 12:05:11

then concatenate the value with date part and then check if ISDATE(date+ ' '+time) =1

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-25 : 12:10:32

ISDATE() should work against a time string, returning 1 when it is a valid time, and 0 when it is not.

declare @time varchar(15)
set @time = '15:00:00'
select isdate(@time)

'15:00:00' = 1
'00:00:00' = 1
'00:e0:00' = 0
'99:00:00' = 0

Hope this helps.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-25 : 12:22:49

00E00 is being assumed as exponential...which is why ISNUMERIC is failing.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 15:59:08

WHERE Col1 LIKE '%[0-2][0-9]:[0-5][0-9]:[0-5][0-9]'
AND ISDATE(RIGHT(Col1, 8)) = 1

E 12°55'05.63"
N 56°04'39.26"

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-26 : 04:29:06

I thought ISDATE will only work for date but not time. Thanks, that solution is nice and clean.

quote:
Originally posted by SQLforGirls

ISDATE() should work against a time string, returning 1 when it is a valid time, and 0 when it is not.

declare @time varchar(15)
set @time = '15:00:00'
select isdate(@time)

'15:00:00' = 1
'00:00:00' = 1
'00:e0:00' = 0
'99:00:00' = 0

Hope this helps.

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-26 : 04:30:35

Thanks for posting rest of the solution guys, this forum has always given me good solution to all me SQL problems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources