[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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

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
bbdesign
Asked:
bbdesign
  • 3
  • 2
1 Solution
 
zvytasCommented:
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
 
bbdesignAuthor Commented:
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
 
bbdesignAuthor Commented:
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
 
zvytasCommented:
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
 
bbdesignAuthor Commented:
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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now