converting time string in temp table to military time then cast as integer

• SQLalchemy

  SSC-Addicted

  Points: 486

  More actions

  December 26, 2014 at 3:29 pm

  #293860

  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?

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  December 27, 2014 at 3:22 am

  #1766543

  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

• Jeff Moden

  SSC Guru

  Points: 1004593

  More actions

  December 31, 2014 at 2:30 pm

  #1767292

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)