Splitting a delimited field

• richardmgreen1

  SSChampion

  Points: 10356

  More actions

  June 30, 2016 at 8:28 am

  #1887896

  Apologies for the formatting......

  Here's how they come directly out of the database.

  As you can see, they are just numbers and I have to format them manually to get them into something reasonable that I can store in the correct datatypes.

  TimeofOnsetofLabourDateofOnsetofLabour

  150020160423

  002020160423

  154020160426

  020020160424

  043520160430

  103720160423

  085520160420

  093720160425

  054020160426

  090020160429

  150020160420

  010020160427

  183020160430

  094620160428

  200020160427

  122020160423

  121520160430

  183020160422

  094520160429

  010020160428

  001920160429

  113020160425

  200020160423

  021520160430

  140020160429

  180020160423

  140020160427

  100020160421

  190020160425

  111020160426

  074020160429

  024020160421

  235020160429

  161020160422

  140020160428

  202020160426

  141520160420

  193020160430

  020020160423

  003020160424

  003220160422

  132520160425

  111020160423

  011020160429

  211520160427

  141520160422

  113520160429

  143020160428

  091020160426

  120020160422

  090020160420

  021120160429

  001520160427

  064520160420

  030020160424

  010020160426

  080020160430

  220020160421

  170020160424

  041520160429

  210020160424

  054020160425

  031220160428

  094120160422

  030020160423

  010020160424

  213020160422

  123020160429

  183020160420

  110020160424

  213820160424

  ::edit::

  There's supposed to be 2 columns there.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 30, 2016 at 9:15 am

  #1887912

  Here's some stuff for you to explore. Your query can be simplified a lot and made much faster...

  IF 0 = 1

  WITH MyDates AS (

  SELECT * FROM (VALUES

  ('1500', '20160423'),('0020', '20160423'),('1540', '20160426'),('0200', '20160424'),('0435', '20160430'),

  ('1037', '20160423'),('0855', '20160420'),('0937', '20160425'),('0540', '20160426'),('0900', '20160429'),

  ('1500', '20160420'),('0100', '20160427'),('1830', '20160430'),('0946', '20160428'),('2000', '20160427'),

  ('1220', '20160423'),('1215', '20160430'),('1830', '20160422'),('0945', '20160429'),('0100', '20160428'),

  ('0019', '20160429'),('1130', '20160425'),('2000', '20160423'),('0215', '20160430'),('1400', '20160429'),

  ('1800', '20160423'),('1400', '20160427'),('1000', '20160421'),('1900', '20160425'),('1110', '20160426'),

  ('0740', '20160429'),('0240', '20160421'),('2350', '20160429'),('1610', '20160422'),('1400', '20160428'),

  ('2020', '20160426'),('1415', '20160420'),('1930', '20160430'),('0200', '20160423'),('0030', '20160424'),

  ('0032', '20160422'),('1325', '20160425'),('1110', '20160423'),('0110', '20160429'),('2115', '20160427'),

  ('1415', '20160422'),('1135', '20160429'),('1430', '20160428'),('0910', '20160426'),('1200', '20160422'),

  ('0900', '20160420'),('0211', '20160429'),('0015', '20160427'),('0645', '20160420'),('0300', '20160424'),

  ('0100', '20160426'),('0800', '20160430'),('2200', '20160421'),('1700', '20160424'),('0415', '20160429'),

  ('2100', '20160424'),('0540', '20160425'),('0312', '20160428'),('0941', '20160422'),('0300', '20160423'),

  ('0100', '20160424'),('2130', '20160422'),('1230', '20160429'),('1830', '20160420'),('1100', '20160424'),

  ('2138', '20160424')

  ) d (TimeofOnsetofLabour, DateofOnsetofLabour)

  ) SELECT *

  INTO #SampleData

  FROM MyDates;

  set dateformat dmy;

  declare @start datetime, @end datetime;

  set @start=cast(DATEADD(month,-2,getdate())-DATEPART(d,getdate())+1 as date);

  set @end=cast(DATEADD(month,-1,getdate())-DATEPART(d,getdate()) as date);

  SELECT @start, @end;

  SELECT

  DateofOnsetofLabour,

  TimeofOnsetofLabour,

  [LabourOnsetDateTimeOneWay] = cast(

  dateadd(minute,cast(right(q2.TimeofOnsetofLabour,2) as int),

  dateadd(hour,cast(left(q2.TimeofOnsetofLabour,2) as int),

  cast(right(q2.DateofOnsetofLabour,2) + '-' +

  substring(q2.DateofOnsetofLabour,5,2) + '-' + left(q2.DateofOnsetofLabour,4) as datetime)

  )

  ) as datetime),

  [LabourOnsetDateTimeAnotherway] = CONVERT(DATETIME,STUFF(STUFF(DateofOnsetofLabour,5,0,'-'),8,0,'-') + ' ' + STUFF(TimeofOnsetofLabour,3,0,':'),120),

  [LabourOnsetDateOnly] = CONVERT(DATE,DateofOnsetofLabour,112)

  FROM #SampleData q2

  WHERE DateofOnsetofLabour BETWEEN CONVERT(VARCHAR(1250),@start,112) AND CONVERT(VARCHAR(1250),@end,112);

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden