SQL Server 2005 Cast-Convert Date Time Format

Using CAST function in SQL Server 2005:

Syntax

CAST(expression as datatype)

CAST function accepts expression name as column name or any SQL function that return expression or value and declare the datatype of the expression into which you want to cast/convert it. 

Cast DateTime into varchar

Example 1

USE Pubs

SELECT CAST(pubdate as varchar) FROM titles 

Example 2

SELECT CAST(getdate() as varchar)

Convert Time format

Convert Time Format into hh:mm:ss

SELECT CONVERT(varchar, getdate(), 108)

Convert Time Format into hh:mi:ss:mmm(24h)

SELECT CONVERT(varchar, getdate(), 114)

You can use datetime type column name instead of getdate() function.

Example 1

USE PUBS

SELECT CONVERT(varchar, pubdate, 108) FROM titles

Example 2

SELECT CONVERT(varchar, pubdate, 114) FROM titles

Also read the tutorial: SQL Server 2005 Convert DateTime Format to learn about the formatted output for date time values.