?
Solved

converting varchar column to time column - trying not to lose data or be incompatible with future data

Posted on 2011-09-08
5
Medium Priority
?
294 Views
Last Modified: 2012-05-12
I have two columns in a table: datestamp and timestamp. Both were varchar columns. I changed datestamp to a "date" column type to improve efficiency. Attached is what my existing data for timestamp looks like. Can I just change the column type and keep this data? What is the best column datatype to change to? Essentially what I am doing when I return data from this table is combine the two columns to create a datetime column. I am used to using datetime columns, I have never stored dates and times separately before, so would appreciate any advice.

A second question would be, after I change the varchar to a time column, would something like this be valid?

order by datestamp+' '+timestamp

...in other words, can I combine those two columns in a query to create a datetime value?

Thank you!
timestamp
01:34:25
01:34:45
01:35:05
01:35:25
01:35:45
01:36:05
01:36:25
01:36:45
01:37:05
01:37:25
01:37:45
01:38:05
01:38:25
01:38:45
01:39:05
01:39:25
01:39:45
01:40:05
01:40:25
01:40:45
01:41:05
01:41:25
01:41:45
01:42:05
01:42:25
01:42:45
01:43:05
01:43:25
01:43:45
01:44:05
01:44:20
01:46:20
01:46:40
01:47:00
01:47:20
01:47:40
01:48:00
01:48:20
01:48:40
01:49:00
01:49:20
01:49:40
01:50:00
01:50:20
01:50:40
01:51:00
01:51:20
01:51:40
01:52:00
01:52:20
01:52:40
01:53:00
01:53:20
01:53:40
01:54:00
01:54:20
01:54:40
01:55:00
01:55:20
01:55:40
01:56:00
01:56:20
01:56:40
01:57:00
01:57:20
01:57:40
01:58:00
01:58:20
01:58:40
01:59:00
01:59:20
01:59:40
02:00:00
02:00:20
02:00:40
02:01:00
02:01:20
02:01:40
02:02:00
02:02:20
02:02:40
02:03:00
02:03:20
02:03:40
02:04:00
02:04:20
02:04:40
02:05:00
02:05:20
02:05:40
02:06:00
02:06:20
02:06:40
02:07:00
02:07:20
02:07:40
02:08:00
02:08:20
02:08:40
02:09:00
02:09:20
02:09:40
02:10:00
02:10:20
02:10:40
02:11:00
02:11:20
02:11:40
02:12:00
02:12:20
02:12:40
02:13:00
02:13:20
02:13:40
02:14:00
02:14:20
02:14:40
02:15:00
02:15:20
02:15:40
02:16:00
02:16:20
02:16:34
03:17:37
03:17:57
03:18:17
03:18:37
03:18:57
03:18:58
03:19:17
03:19:37
03:19:57
03:20:17
03:20:37
03:20:57
03:21:17
03:21:37
03:21:57
03:22:17
03:22:37
03:22:57
03:23:17
03:23:37
03:23:57
03:24:17
03:24:37
03:24:57
03:25:17
03:25:37
03:25:57
03:26:17
03:26:37
03:26:57
03:27:17
03:27:37
03:27:41
03:28:20
03:28:40
03:29:00
03:29:20
03:29:40
03:30:00
03:30:20
03:30:40
03:31:00
03:31:20
03:31:40
03:32:00
03:32:20
03:32:40
03:33:00
03:33:20
03:33:40
03:34:00
03:34:20
03:34:40
03:35:00
03:35:20
03:35:40
03:36:00
03:36:20
03:36:40
03:37:00
03:37:20
03:37:40
03:38:00
03:38:20
03:38:40
03:39:00
03:39:20
03:39:40
03:40:00
03:40:20
03:40:40
03:41:00
03:41:20
03:41:40
03:42:00
03:42:20
03:42:40
03:43:00
03:43:20
03:43:40
03:44:00
03:44:20
03:44:38
03:44:40
03:44:58
03:45:00
03:45:02
03:45:18
03:45:20
03:45:38
03:45:40
03:45:58
03:46:00
03:46:18
03:46:20
03:46:35
03:46:40
03:47:00
03:47:20
03:47:36
03:51:05
03:51:25
03:51:45
03:52:05
03:52:25
03:52:45
03:53:05
03:53:25
03:53:29
03:54:49
03:55:09
03:55:29
03:55:49
03:56:09
03:56:29
03:56:49
03:57:09
03:57:29
03:57:49
03:58:09
03:58:29
03:58:49
03:59:09
03:59:29
03:59:49
04:00:09
04:00:29
04:00:49
04:01:09
04:01:29
04:01:49
04:02:09
04:02:29
04:02:49
04:03:09
04:03:29
04:03:49
04:04:09
04:04:29
04:04:49
04:05:09
04:05:29
04:05:49
04:06:09
04:06:29
04:06:49
04:07:09
04:07:29
04:07:49
04:08:09
04:08:29
04:08:49
04:09:09
04:09:29
04:09:49
04:10:09
04:10:29
04:10:49
04:11:09
04:11:29
04:15:00
04:15:20
04:15:39
04:15:40
04:15:59
04:16:00
04:16:19
04:16:20
04:16:39
04:16:40
04:16:59
04:17:00
04:17:19
04:17:20
04:17:39
04:17:40
04:17:59
04:18:00
04:18:19
04:18:20
04:18:39
04:18:40
04:18:59
04:19:00
04:19:19
04:19:20
04:19:39
04:19:40
04:19:59
04:20:00
04:20:19
04:20:20
04:20:39
04:20:40
04:20:59
04:21:00
04:21:19
04:21:20
04:21:39
04:21:40
04:21:59
04:22:00
04:22:19
04:22:20
04:22:39
04:22:40
04:22:59
04:23:00
04:23:19
04:23:20
04:23:39
04:23:40
04:23:59
04:24:00
04:24:19
04:24:20
04:24:39
04:24:40
04:24:59
04:25:00
04:25:19
04:25:20
04:25:39
04:25:40
04:25:59
04:26:00
04:26:19
04:26:20
04:26:39
04:26:40
04:26:59
04:27:00
04:27:19
04:27:20
04:27:39
04:27:40
04:27:59
04:28:00
04:28:19
04:28:20
04:28:39
04:28:40
04:28:59
04:29:00
04:29:19
04:29:20
04:29:39
04:29:40
04:29:59
04:30:00
04:30:19
04:30:20
04:30:39
04:30:40
04:30:59
04:31:00
04:31:19
04:31:20
04:31:40
04:32:00
04:32:20
04:32:40
04:33:00
04:33:20
04:33:40
04:34:00
04:34:20
04:34:40
04:35:00
04:35:20
04:35:40
04:36:00
04:36:20
04:36:40
04:37:00
04:37:20
04:37:39
05:31:18
05:31:38
05:31:58
05:32:18
05:32:38
05:32:58
05:33:18
05:33:38
05:33:58
05:34:18
05:34:38
05:34:58
05:35:18
05:35:38
05:35:58
05:36:18
05:36:20
05:36:38
05:36:58
05:37:18
05:37:38
05:37:58
05:38:18
05:38:38
05:38:58
05:39:18
05:39:38
05:39:58
05:40:18
05:40:38
05:40:58
05:41:18
05:41:38
05:41:58
05:42:18
05:42:38
05:42:58
05:43:00
05:43:18
05:43:38
05:43:58
05:44:01
05:44:18
05:44:21
05:44:38
05:44:58
05:45:18
05:45:38
05:45:58
05:46:18
05:46:38
05:46:58
05:47:01
05:47:18
05:47:38
05:47:58
05:48:18
05:48:38
05:48:58
05:49:02
05:49:18
05:49:38
05:49:58
05:50:18
05:50:38
05:50:41
05:50:58
05:51:18
05:51:38
05:51:41
05:51:58
05:52:18
05:52:38
05:52:58
05:53:06
05:53:51
05:53:52
05:54:11
05:54:31
05:54:51
05:55:11
05:55:31
05:55:51
05:56:11
05:56:31
05:56:51
05:56:53
05:57:11
05:57:31
05:57:51
05:58:11
05:58:31
05:58:51
05:59:11
05:59:31
05:59:51
06:00:11
06:00:31
06:00:51
06:01:11
06:01:31
06:01:51
06:02:11
06:02:31
06:02:51
06:03:11
06:03:31
06:03:51
06:04:11
06:04:31
06:04:51
06:05:11
06:05:31
06:05:51
06:06:11
06:06:14
06:06:31
06:06:35
06:46:02
06:46:22
06:46:23
06:46:42
06:46:43
06:47:02
06:47:03
06:47:04
06:47:22
06:47:23
06:47:42
06:47:43
06:48:00
06:48:02
06:48:22
06:48:33
06:48:40
06:48:42
06:49:02
06:49:22
06:49:23
06:49:42
06:49:43
06:50:02
06:50:03
06:50:22
06:50:23
06:50:42
06:50:43
06:51:02
06:51:03
06:51:22
06:51:23
06:51:42
06:51:43
06:52:02
06:52:22
06:52:42
06:53:02
06:53:07
06:53:22
06:53:27
06:53:42
06:53:47
06:54:02
06:54:07
06:54:22
06:54:27
06:54:42
06:54:47
06:55:02
06:55:07
06:55:22
06:55:25
06:55:27
06:55:47
06:56:07
06:56:09
06:56:27
06:56:29
06:56:47
06:56:49
06:57:07
06:57:09
06:57:27
06:57:29
06:57:47
06:57:49
06:58:07
06:58:09
06:58:27
06:58:29
06:58:47
06:58:49
06:59:07
06:59:09
06:59:27
06:59:29
06:59:47
06:59:49
07:00:07
07:00:09
07:00:27
07:00:29
07:00:47
07:00:49
07:01:07
07:01:09
07:01:27
07:01:29
07:01:47
07:01:49
07:02:07
07:02:09
07:02:27
07:02:29
07:02:47
07:02:49
07:03:07
07:03:09
07:03:27
07:03:29
07:03:47
07:03:49
07:04:07
07:04:09
07:04:27
07:04:29
07:04:47
07:04:49
07:05:07
07:05:09
07:05:27
07:05:29
07:05:47
07:05:49
07:06:07
07:06:09
07:06:27
07:06:29
07:06:47
07:06:49
07:07:07
07:07:09
07:07:27
07:07:29
07:07:47
07:07:49
07:08:07
07:08:09
07:08:27
07:08:29
07:08:47
07:08:49
07:09:07
07:09:09
07:09:27
07:09:29
07:09:47
07:09:49
07:10:07
07:10:09
07:10:27
07:10:29
07:10:47
07:10:49
07:11:07
07:11:09
07:11:27
07:11:29
07:11:47
07:11:49
07:12:07
07:12:09
07:12:27
07:12:29
07:12:47
07:12:49
07:13:07
07:13:09
07:13:27
07:13:29
07:13:47
07:13:49
07:14:07
07:14:09
07:14:27
07:14:29
07:14:47
07:14:49
07:15:07
07:15:09
07:15:27
07:15:29
07:15:47
07:15:49
07:16:07
07:16:09
07:16:27
07:16:29
07:16:47
07:16:49
07:17:07
07:17:09
07:17:27
07:17:29
07:17:47
07:17:49
07:18:07
07:18:09
07:18:27
07:18:29
07:18:47
07:18:49
07:19:07
07:19:09
07:19:27
07:19:29
07:19:47
07:19:49
07:19:54
07:20:09
07:20:29
07:20:49
07:21:09
07:21:29
07:21:49
07:22:09
07:22:29
07:22:49
07:23:09
07:23:29
07:23:49
07:24:09
07:24:29
07:24:49
07:25:09
07:25:29
07:25:49
07:26:06
08:26:04
08:29:57
08:30:17
08:30:37
08:30:57
08:31:17
08:31:37
08:31:57
08:32:17
08:32:37
08:32:57
08:33:17
08:33:37
08:33:57
08:34:17
08:34:37
08:34:57
08:35:17
08:35:37
08:35:57
08:36:17
08:36:37
08:36:57
08:37:17
08:37:37
08:37:57
08:38:17
08:38:37
08:38:57
08:39:17
08:39:37
08:39:57
08:40:17
08:40:37
08:40:57
08:41:17
08:41:37
08:41:57
08:42:17
08:42:37
08:42:57
08:43:17
08:43:37
08:43:57
08:44:17
08:44:37
08:44:57
08:45:17
10:04:50
10:05:10
10:05:30
10:05:50
10:06:10
10:06:30
10:06:50
10:07:10
10:07:30
10:07:50
10:08:10
10:08:30
10:08:50
10:09:10
10:09:30
10:09:50
10:10:10
10:10:30
10:10:50
10:11:10
10:11:30
10:11:50
10:12:10
10:12:30
10:12:50
10:13:10
10:13:30
10:13:50
10:14:10
10:14:30
10:14:50
10:15:10
10:15:30
10:15:50
10:16:10
10:16:30
10:16:50
10:17:10
10:17:30
10:17:50
10:18:10
10:18:30
10:18:50
10:19:10
10:19:30
10:19:50
10:20:10
10:20:30
10:20:50
10:21:10
10:21:30
10:21:50
10:22:10
10:22:30
10:22:50
10:23:10
10:23:30
10:23:50
10:24:10
10:24:30
10:24:50
10:25:10
10:25:30
10:25:50
10:26:10
10:26:30
10:26:50
10:27:10
10:27:30
10:27:50
10:28:10
10:28:30
10:28:50
10:29:10
10:29:30
10:29:50
10:30:10
10:30:30
10:30:50
10:31:10
10:31:30
10:31:50
10:32:10
10:32:30
10:32:50
10:33:10
10:33:30
10:33:50
10:34:10
10:34:30
10:34:50
10:35:10
10:35:30
10:35:50
10:36:10
10:36:30
10:36:50
10:37:10
10:37:30
10:37:50
10:38:10
10:38:30
10:38:50
10:39:10
10:39:30
10:39:50
10:40:10
10:40:30
10:40:50
10:41:10
10:41:30
10:41:50
10:42:10
10:42:30
10:42:50
10:43:10
10:43:30
10:43:50
10:44:10
10:44:30
10:44:50
10:45:10
10:45:30
10:45:50
10:46:10
10:46:30
10:46:50
10:47:10
10:47:30
10:47:50
10:48:10
10:48:30
10:48:50
10:49:10
10:49:30
10:49:50
10:50:10
10:50:30
10:50:50
10:51:10
10:51:30
10:51:50
10:52:10
10:52:30
10:52:50
10:53:10
10:53:30
10:53:50
10:54:10
10:54:30
12:06:42
12:07:02
12:07:22
12:07:42
12:08:02
12:08:22
12:08:42
12:09:02
12:09:22
12:09:42
12:10:02
12:10:22
12:10:42
12:11:02
12:11:22
12:11:42
12:12:02
12:12:22
12:12:42
12:13:02
12:13:22
12:13:42
12:14:02
12:14:08
12:14:22
12:14:28
12:14:42
12:15:02
12:15:22
12:15:42
12:16:02
12:16:22
12:16:42
12:17:02
12:17:22
12:17:42
12:18:02
12:18:22
12:18:42
12:19:02
12:19:22
12:19:42
12:20:02
12:20:22
12:20:42
12:21:02
12:21:22
12:21:42
12:22:02
12:22:22
12:22:42
12:23:02
12:23:22
12:23:42
12:24:02
12:24:22
12:24:42
12:25:02
12:25:22
12:25:42
12:26:02
12:26:22
12:26:42
12:27:02
12:27:22
12:27:42
12:28:02
12:28:22
12:28:42
12:29:02
12:29:22
12:29:42
12:29:51
12:30:02
12:30:04
12:30:11
12:30:22
12:30:24
12:30:27
12:30:42
12:30:44
12:30:55
12:31:02
12:31:22
12:31:42
12:32:02
12:32:22
12:32:42
12:33:02
12:33:22
12:33:42
12:34:02
12:34:22
12:34:42
12:35:02
12:35:22
16:02:57
16:03:17
16:03:37
16:03:57
16:04:17
16:04:32
16:04:47
17:45:39
17:45:59
17:46:19
17:46:39
17:46:59
17:47:19
17:47:39
17:47:59
17:48:19
17:48:39
17:48:59
17:49:19
17:49:39
17:49:59
17:50:19
17:50:39
17:50:59
17:51:19
17:51:39
17:51:59
17:52:19
17:52:39
17:52:59
17:53:19
17:53:39
17:53:59
17:54:19
17:54:39
17:54:59
17:55:19
17:55:39
17:55:59
17:56:19
17:56:39
17:56:59
17:57:19
17:57:39
17:57:59
17:58:19
17:58:39
17:58:59
17:59:19
17:59:39
17:59:59
18:00:19
18:00:39
18:00:59
18:01:19
18:01:39
18:01:59
18:02:19
18:02:39
18:02:59
18:03:19
18:03:39
18:03:59
18:04:19
18:04:39
18:04:59
18:05:19
18:05:39
18:05:59
18:06:19
18:06:39
18:06:59
18:07:19
18:07:39
18:07:59
18:08:19
18:08:39
18:08:59
18:09:19
18:09:39
18:09:59
18:10:19
18:10:39
18:10:59
18:11:19
18:11:39
18:11:59
18:12:19
18:12:39
18:12:59
18:13:19
18:13:39
18:13:59
18:14:19
18:14:39
18:14:59
18:15:19
18:15:39
18:15:59
18:16:19
18:16:39
18:16:59
18:17:19
19:13:32
19:13:52
19:14:12
19:14:32
19:14:52
19:14:55
19:15:46
19:16:06
19:16:26
19:16:46
19:17:06
19:17:26
19:17:46
19:18:06
19:18:26
19:18:46
19:19:06
19:19:26
19:19:46
19:20:06
19:20:26
19:20:46
19:21:06
19:21:26
19:21:46
19:22:06
19:22:26
19:22:46
19:23:06
19:23:26
19:23:46
19:24:06
19:24:26
19:24:46
19:25:06
19:25:26
19:25:46
19:26:06
19:26:26
19:26:46
19:27:06
19:27:26
19:27:46
19:28:06
19:28:26
19:28:46
19:29:06
19:29:26
19:29:46
19:30:06
19:30:26
19:30:46
19:31:06
19:31:26
19:31:46
19:32:06
19:32:26
19:32:46
19:33:06
19:33:26
19:33:46
19:34:06
19:34:26
19:35:55
19:36:15
19:36:35
19:36:55
19:37:15
19:37:35
19:37:55
19:38:15
19:38:35
19:38:55
19:39:15
19:39:35
19:39:55
19:40:15
19:40:35
19:40:55
19:41:15
19:41:35
19:41:55
19:42:15
19:42:35
19:42:55
19:43:15
19:43:35
19:43:55
19:44:15
19:44:35
19:44:55
19:45:15
19:45:35
19:45:55
19:46:15
19:46:35
19:46:55
19:47:15
19:47:35
19:47:55
19:48:15
19:48:35
19:48:55
19:49:15
19:49:35
19:49:55
19:50:15
19:50:35
19:50:55
19:51:15
19:51:35
19:51:55
19:52:15
19:52:35
19:52:55
19:53:15
19:53:35
19:53:55
19:54:15
19:54:35
19:54:55
19:55:15
19:55:35
19:55:55
19:56:15
19:56:35
19:56:55
19:57:15
19:57:35
19:57:55
19:58:15
19:58:35
19:58:55
19:59:15
19:59:24
20:13:10
20:13:30
20:13:50
20:14:10
20:14:30
20:14:50
20:15:10
20:15:30
20:15:50
20:16:10
20:16:30
20:16:50
20:17:10
20:17:30
20:17:50
20:18:10
20:18:30
20:18:50
20:19:10
20:19:30
20:19:50
20:20:10
20:20:30
20:20:50
20:21:10
20:21:30
20:21:50
20:22:10
20:22:30
20:22:50
20:23:10
20:23:30
20:23:50
20:24:10
20:24:30
20:24:50
20:25:10
20:25:30
20:25:50
20:26:10
20:26:30
20:26:50
20:27:10
20:27:30
20:27:50
20:27:51
20:28:48
20:29:08
20:29:28
20:29:48
20:30:08
20:30:28
20:30:48
20:31:08
20:31:28
20:31:48
20:32:08
20:32:28
20:32:48
20:33:08
20:33:28
20:33:48
20:34:08
20:34:28
20:34:48
20:35:08
20:35:28
20:35:48
20:36:08
20:36:28
20:36:48
20:37:08
20:37:28
20:37:48
20:38:08
20:38:28
20:38:48
20:39:08
20:39:28
20:39:48
20:40:08
20:40:28
20:40:48
20:41:08
20:41:28
20:41:48
20:42:08
20:42:28
20:42:48
20:43:08
20:43:28
20:43:48
20:44:08
20:44:28
20:44:48
20:45:08
20:45:28
20:45:48
20:46:08
20:46:28
20:46:48
20:47:08
20:47:28
20:47:48
20:48:08
20:48:28
20:48:48
20:49:08
20:49:28
20:49:48
20:50:08
20:50:28
20:50:48
20:51:08
20:51:28
20:51:48
20:52:08
20:52:28
20:52:48
20:53:08
20:53:28
20:53:48
20:54:08
20:54:28
20:54:48
20:55:08
20:55:28
20:55:48
20:56:08
20:56:28
20:56:48
20:57:08
20:57:28
20:57:48
20:58:08
20:58:28
20:58:48
20:59:08
20:59:28
20:59:48
21:00:08
21:00:28
21:00:48
21:01:08
21:01:28
21:01:48
21:02:08
21:02:28
21:02:48
21:03:08
21:03:28
21:03:48
21:04:08
21:04:28
21:04:30
21:53:16
21:53:36
21:53:56
21:54:16
21:54:36
21:54:56
21:55:16
21:55:36
21:55:56
21:56:16
21:56:36
21:56:56
21:57:16
21:57:36
21:57:56
21:58:16
21:58:36
21:58:56
21:59:16
21:59:36
21:59:56
22:00:16
22:00:36
22:00:56
22:01:16
22:01:36
22:01:56
22:02:16
22:02:36
22:02:39
24:06:15
24:06:35
24:06:55
24:07:15
24:07:35
24:07:55
24:08:15
24:08:35
24:08:55
24:09:15
24:09:35
24:09:55
24:10:15
24:10:35

Open in new window

0
Comment
Question by:Brad Bansner
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:zvytas
ID: 36503414
I assume you're using MS SQL 2008 - if that's the case you can use Time as a column type. Ordering should be done as such:

order by datestamp, timestamp
0
 

Author Comment

by:Brad Bansner
ID: 36503442
Do I want time(7) then? Looking at my sample data, you believe it will convert over OK? Just trying to be as sure as I can. Thanks.
0
 

Author Comment

by:Brad Bansner
ID: 36503479
I tried changing the column and saving, and it says:

'tbl_userdata' table
- Unable to modify table.  
Conversion failed when converting date and/or time from character string.

So I guess something about my sample data isn't fitting the format?
0
 
LVL 5

Accepted Solution

by:
zvytas earned 2000 total points
ID: 36503660
It fails because you have times where hour is set to 24. SQL expects hours to be from 0 to 23, not 1 to 24.

Before changing type just run the following update statetment:

UPDATE Table
SET Time = '00:' + SUBSTRING(Time, 4, 20)
WHERE Time like '24:%'

This will change 24:.. to 00:...

P.S. Time(7) is correct.
0
 

Author Comment

by:Brad Bansner
ID: 36503879
Great, thanks for the info!
0

Featured Post

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.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

589 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