Solved

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

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
help converting varchar to date 14 25
SQL Syntax 6 32
Find special characters using tSQL 6 19
Can a Trigger trigger a Trigger? 4 18
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

733 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