Azure Data Bricks – Convert to Date for Ordering

I have had to construct a string that has a millisecond compoment as source where data is being extracted does not allow a date field to hold a milli second component. However not the problem ..

Query

   select POWERAPPCAPTURETIME, POWERAPPSECONDS,POWERAPPMILLISECONDS,
  
   replace(
        concat_ws(':', 
            regexp_replace(date_format(POWERAPPCAPTURETIME, 'dd/M/yyyy HH:mm'), '[a-z]', ''), CaSe when POWERAPPSECONDS='0' then '00' else POWERAPPSECONDS end, case when POWERAPPMILLISECONDS='0' then '000' else POWERAPPMILLISECONDS end
        ), 
        '.', ':'
    ) as DATE_TIME_WITH_MILLISECONDS_STRING,
  
  
  to_date (
   replace(
        concat_ws(':', 
            regexp_replace(date_format(POWERAPPCAPTURETIME, 'dd/M/yyyy HH:mm'), '[a-z]', ''), CaSe when POWERAPPSECONDS='0' then '00' else POWERAPPSECONDS end, case when POWERAPPMILLISECONDS='0' then '000' else POWERAPPMILLISECONDS end
        ), 
        '.', ':'
    ) , "dd/M/yyyy HH:mm:ss:sss" ) as DATE_TIME_WITH_MILLISECONDS_TIMESTAMP from Table1

The problem is that I can not order records in Azure Data Bricks Sql Analaytics using the STRING version of the field . I need to change it to timestamp data type to do this but cant seem to get the syntax right . This is what my query gives me currently :

Answer

Got this to work now . Syntax issue POWERAPPCAPTURETIME, ‘dd/M/yyyy HH:mm’. This then works for partition by and ordering using that column .

Advertisement

Answer