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:
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)
Nice script. Helped me in getting current time.
ReplyDeleteDECLARE
@StrTime CHAR(5) = CAST(GETDATE() AS TIME),
@Time TIME
SELECT @Time = STUFF(@StrTime,3,1,':')
SELECT CONVERT(VARCHAR(20), @Time, 100)