?
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
?
279 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:bbdesign
[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
  • 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:bbdesign
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:bbdesign
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:bbdesign
ID: 36503879
Great, thanks for the info!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

762 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