Tuesday, September 24, 2013

How to convert a "CHAR(4)" datatype to "TIME" datatype

This question was asked on MSDN SQL forum on below link and I proposed the solution and thought to place the same T-SQL here on my blog as well.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/89e02e90-95ec-46c5-98ea-f649662a3dbb/how-to-convert-a-char4-datatype-to-time-datatype

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2f8e6726-f777-4e4d-aa4a-2277adf863d0/convert-varchar4-to-time

Scenario:

I have a "char(4)" column whose values I need to change to a "time" datatype in 12 hour format.  For example, I have "1600" and I need to change that to "4:00pm".  I don't need to update the values in the database, only in a report.  Can I do this is t-sql?  I'm using SQL Server 2008 R2.

Solution:

DECLARE
      @StrTime CHAR(4) = '1600',
      @Time TIME
SELECT @Time = STUFF(@StrTime,3,1,':')

SELECT CONVERT(VARCHAR(20), @Time, 100)


1 comment:

  1. Nice script. Helped me in getting current time.

    DECLARE
    @StrTime CHAR(5) = CAST(GETDATE() AS TIME),
    @Time TIME
    SELECT @Time = STUFF(@StrTime,3,1,':')
    SELECT CONVERT(VARCHAR(20), @Time, 100)

    ReplyDelete