SELECT convert (varchar, getdate (), 100) -- mon dd yyyy hh: mmAM (or PM)
-- Oct 2 2008 AM
SELECT converter (varchar, getdate (), 101) -- mm/dd/yyyy-10/02/2008
SELECT convert (varchar, getdate (), 102) -- yyyy. mm. dd -- 2008.10.02
SELECT convert (varchar, getdate (), 103) -- dd/mm/yyyy
SELECT convert (varchar, getdate (), 104) -- dd. mm. yyyy
SELECT convert (varchar, getdate (), 105) -- dd-mm-yyyy
SELECT convert (varchar, getdate (), 106) -- dd mon yyyy
SELECT convert (varchar, getdate (), 107) -- mon dd, yyyy
SELECT convert (varchar, getdate (), 108) -- hh: mm: ss
SELECT convert (varchar, getdate (), 109) -- mon dd yyyy hh: mm: ss: mmmAM (or PM)
-- Oct 2 2008 11: 02: 44: 013 AM
SELECT convert (varchar, getdate (), 110) -- mm-dd-yyyy
SELECT convert (varchar, getdate (), 111) -- yyyy/mm/dd
SELECT convert (varchar, getdate (), 112) -- yyyymmdd
SELECT convert (varchar, getdate (), 113) -- dd mon yyyy hh: mm: ss: mmm
-- 02 Oct 2008 11: 02: 07: 577
SELECT convert (varchar, getdate (), 114) -- hh: mm: ss: mmm (24 h)
SELECT convert (varchar, getdate (), 120) -- yyyy-mm-dd hh: mm: ss (24 h)
SELECT convert (varchar, getdate (), 121) -- yyyy-mm-dd hh: mm: ss. mmm
SELECT convert (varchar, getdate (), 126) -- yyyy-mm-ddThh: mm: ss. mmm
-- Maid: 52: 47.513
-- Create different date formats using string functions
SELECT replace (convert (varchar, getdate (), 111), '/', '') -- yyyy mm dd
SELECT convert (varchar (7), getdate (), 126) -- yyyy-mm
SELECT right (convert (varchar, getdate (), 106), 8) -- mon yyyy
Go
-- General date conversion function create function dbo. fnFormatDate (@ Datetime DATETIME, @ FormatMask VARCHAR (32) returns varchar (32)
BEGIN
DECLARE @ StringDate VARCHAR (32)
SET @ StringDate = @ FormatMask
IF (CHARINDEX ('yyyy', @ StringDate)> 0)
SET @ StringDate = REPLACE (@ StringDate, 'yyyy', DATENAME (YY, @ Datetime ))
IF (CHARINDEX ('yy', @ StringDate)> 0)
SET @ StringDate = REPLACE (@ StringDate, 'yy', RIGHT (DATENAME (YY, @ Datetime), 2 ))
IF (CHARINDEX ('month', @ StringDate)> 0)
SET @ StringDate = REPLACE (@ StringDate, 'month', DATENAME (MM, @ Datetime ))
IF (CHARINDEX ('Mon', @ StringDate COLLATE SQL _Latin1_General_CP1_CS_AS)> 0)
SET @ StringDate = REPLACE (@ StringDate, 'Mon', LEFT (UPPER (DATENAME (MM, @ Datetime), 3 ))
IF (CHARINDEX ('Mon', @ StringDate)> 0)
SET @ StringDate = REPLACE (@ StringDate, 'Mon', LEFT (DATENAME (MM, @ Datetime), 3 ))
IF (CHARINDEX ('mm', @ StringDate)> 0)
SET @ StringDate = REPLACE (@ StringDate, 'mm', RIGHT ('0' + CONVERT (VARCHAR, DATEPART (MM, @ Datetime), 2 ))
IF (CHARINDEX ('M', @ StringDate)> 0)
SET @ StringDate = REPLACE (@ StringDate, 'M', CONVERT (VARCHAR, DATEPART (MM, @ Datetime )))
IF (CHARINDEX ('DD', @ StringDate)> 0)
SET @ StringDate = REPLACE (@ StringDate, 'DD', right ('0' + DATENAME (DD, @ Datetime), 2 ))
IF (CHARINDEX ('D', @ StringDate)> 0)
SET @ StringDate = REPLACE (@ StringDate, 'D', DATENAME (DD, @ Datetime ))
RETURN @ StringDate
END
GO |
