Data Type Conversion

/*  Date and time formatting */

use AAD

select getdate()

select datepart(day,getdate())

select month(getdate()), day(getdate()), year(getdate())

-- convert date into YYYYMMDD string

select convert(varchar(10),getdate(),112)

-- Date in YYYY-MM-DD format - used in calling USP for interface

select convert(varchar(10),getdate(),121)

-- get system date - datetime format

select getdate()

-- get date in mm/dd/yyyy format - string

select convert(varchar,getdate(),101)

-- get system time without time delimiter ":"

select replace(convert(varchar(8),(getdate()),108),':','')

-- get system time with time delimiter

select convert(varchar(8), getdate(), 108)

-- get beginning date in datetime format - equivalent of TRUNC in ORACLe

select cast(floor(cast(getdate() as decimal(12,5))) as datetime)

-- converting string date to datetime value

select convert(datetime,'2 June 2013 00:00:00.000',113)