December 26, 2014 at 3:29 pm
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
Can anyone give some ideas how this can be done while my temp table is in session?
December 27, 2014 at 3:22 am
SQLalchemy (12/26/2014)
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
Can anyone give some ideas how this can be done while my temp table is in session?
Quick example that should get you passed this hurdle
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* Drop the temp table if it exists */
IF OBJECT_ID(N'tempdb..#TMP_TIME') IS NOT NULL DROP TABLE #TMP_TIME;
/* Create sample data set */
DECLARE @SAMPLE_SIZE BIGINT = 25;
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,SAMPLE_BASE AS
(
SELECT
NM.N
,(CHECKSUM(NEWID()) % 43200) AS OFFSET -- offset = +/- 12 hours
,CONVERT(TIME(0),'12:00:00',0) AS BASE_TIME
FROM NUMS NM
)
,SAMPLE_DATA AS
(
SELECT
SB.N
,CONVERT(VARCHAR(10),DATEADD(SECOND,SB.OFFSET, SB.BASE_TIME),109) AS TIME_VAL
FROM SAMPLE_BASE SB
)
/* Create the temp table */
SELECT
SD.N
,SD.TIME_VAL
,CONVERT(VARCHAR(10),'',0) AS STR_TIME
,CONVERT(INT,0,0) AS INT_TIME
INTO #TMP_TIME
FROM SAMPLE_DATA SD;
/* Update the temp table */
UPDATE T
SET T.STR_TIME = REPLACE(CONVERT(VARCHAR(10),CONVERT(TIME(0),T.TIME_VAL,109),108),':','')
,T.INT_TIME = CONVERT(INT,REPLACE(CONVERT(VARCHAR(10),CONVERT(TIME(0),T.TIME_VAL,109),108),':',''),0)
FROM #TMP_TIME T;
SELECT
T.N
,T.TIME_VAL
,T.STR_TIME
,T.INT_TIME
FROM #TMP_TIME T;
Results
N TIME_VAL STR_TIME INT_TIME
---- ---------- ---------- ----------
1 6:36:29PM 183629 183629
2 8:23:36AM 082336 82336
3 10:32:48AM 103248 103248
4 5:28:28AM 052828 52828
5 7:04:28AM 070428 70428
6 10:13:31PM 221331 221331
7 1:52:56AM 015256 15256
8 6:00:57AM 060057 60057
9 6:58:38PM 185838 185838
10 11:08:30AM 110830 110830
11 5:38:22AM 053822 53822
12 8:32:00AM 083200 83200
13 11:23:13AM 112313 112313
14 6:19:35PM 181935 181935
15 9:13:34PM 211334 211334
16 4:59:13PM 165913 165913
17 2:48:06PM 144806 144806
18 12:11:54AM 001154 1154
19 8:51:44PM 205144 205144
20 11:26:12PM 232612 232612
21 2:04:27PM 140427 140427
22 9:16:56AM 091656 91656
23 11:30:47PM 233047 233047
24 7:20:45PM 192045 192045
25 6:32:34PM 183234 183234
December 31, 2014 at 2:30 pm
SQLalchemy (12/26/2014)
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
Can anyone give some ideas how this can be done while my temp table is in session?
Instead of updating the myTable table, add a persisted computed column to it with the following definition...
MovieTime AS CONVERT(INT,REPLACE(CONVERT(CHAR(5),YourTimeColumnHere,108),':','')) PERSISTED
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply