Solved

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

Posted on 2011-09-08
5
276 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 500 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 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