1

I have time format like hh:mm ss in database table. I have written a query to get those values from column.

declare @reg nvarchar(100)
set @reg = '^(?:(?:([01]?\d|2[0-3]):)?([0-5]?\d):)?([0-5]?\d)$'
 select time from table1 where time = @reg

However, its not showing me results. How to get the results?

Edit: Sample data(here is what I want to find from colum):

23:36 26
08:55 46

Here is what I want to change in that column:

23:36:26
08:55:46
AskMe
  • 2,495
  • 8
  • 49
  • 102

2 Answers2

1

The problem is that you are using strings instead of the appropriate type, time. The only real solution is to fix the field type.

To convert the data, you don't need a regex, just replace with : before casting, eg : 

select cast(replace(time,' ',':') as time)

Executing :

select cast(replace('08:55 46',' ',':') as time)

Returns a time value of 08:55:46

You might be tempted to cast time again to a string using the same command, eg: cast(cast(replace(time,' ',':') as time) as nvarchar(8)) but that won't fix the wrong type issue. You'll be forced to parse the text into a time again each time you want to send it to the client or perform time arithmetic.

If you want to use a time variable or time parameter, just specify the appropriate type, eg :

declare @myParam time ='23:12:34'

Or 

Create Procedure MyProc (@myTimeParam time)
...
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • I have 1Million time records in this format in the column (in a table). How can I resolve all these by hard coding? Alos, time format type is char – AskMe Jun 16 '17 at 09:47
  • Thanks for the clue: Update DPUCID01 set time2 = replace(time2, ' ', ':') WHERE time2 like '[0-9][0-9]:[0-9][0-9] [0-9][0-9]' – AskMe Jun 16 '17 at 13:58
0

Check this. It seems SQL SERVER does not support pure regex, you must change your query to something like this:

declare @reg nvarchar(100)
set @reg = '[01][0-9]:[0-5][0-9]:[0-5][0-9]'
select time from table1 where time like @reg
dloeda
  • 1,516
  • 15
  • 23