Search code examples
sqlsql-serverdatetimeselectsql-timestamp

How to convert round number to data and time format

Two Column in table tblpress

Date          Time
20160307      120949
20160307      133427

Need to be select below the format:

07-03-2016 12:09:49 07-03-2016 13:34 27

or

03-March-2016   12:09: 49 PM
03-March-2016   01:34: 27 PM
Solution

  • I think CAST/CONVERT will help you:

    SELECT
  CAST('20160307' AS date),
  CAST(STUFF(STUFF('120949',3,0,':'),6,0,':') AS time)

    And convert for out:

    SELECT
  CONVERT(varchar(20),NormalDate,105) OutDate, -- Italian style
  CONVERT(varchar(20),NormalTime,108) OutTime -- hh:mi:ss
FROM
  (
    SELECT
      CAST([Date] AS date) NormalDate,
      CAST(STUFF(STUFF([Time],3,0,':'),6,0,':') AS time) NormalTime
    FROM YourTable
  ) q

    CAST and CONVERT (Transact-SQL)

    And you can use FORMAT (Transact-SQL)

    SELECT
  FORMAT(GETDATE(),'dd-MM-yyyy'),
  FORMAT(GETDATE(),'HH:mm:ss')