Contact Us

Use the form on the right to contact us.

You can edit the text in this area, and change where the contact form on the right submits to, by entering edit mode using the modes on the bottom right. 


Oak Ridge, TN, 37830
United States

Sql-Sangria

Date Formats

Wade Cantley

"It's A Date" Cider

  • 6 oz. apple cider (heated)
  • 2 oz. Tuaca liqueur
  • whipped cream
  • pinch of cinnamon and nutmeg
  • 1 cinnamon stick

With credit to this blog for laying it out, I am picking out the parts that have been the most useful but by all means, check out this guys blog for more stuff.

http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/


SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)
                                        -- Oct  2 2008 11:01AM
SELECT convert(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:013AM
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(24h)
SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm
                                        -- 2008-10-02T10:52:47.513
-- SQL create different date styles with t-sql 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
-- SQL format datetime
-- Default format: Oct 23 2006 10:40AM
SELECT [Default]=CONVERT(varchar,GETDATE(),100)

-- US-Style format: 10/23/2006
SELECT [US-Style]=CONVERT(char,GETDATE(),101)

-- ANSI format: 2006.10.23
SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)

-- UK-Style format: 23/10/2006
SELECT [UK-Style]=CONVERT(char,GETDATE(),103)

-- German format: 23.10.2006
SELECT [German]=CONVERT(varchar,GETDATE(),104)

-- ISO format: 20061023
SELECT ISO=CONVERT(varchar,GETDATE(),112)

-- ISO8601 format: 2008-10-23T19:20:16.003
SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)
-- Combining different style formats for date & time
-- Datetime formats
-- Datetime formats sql
DECLARE @Date DATETIME
SET @Date = '2015-12-22 03:51 PM'
SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)
-- Result: 12-22-2015  3:51PM

-- Microsoft SQL Server cast datetime to string
SELECT stringDateTime=CAST (getdate() as varchar)
-- Result: Dec 29 2012  3:47AM
----
-- SQL Server date and time functions overview
----
-- SQL Server CURRENT_TIMESTAMP function
-- SQL Server datetime functions
-- local NYC – EST – Eastern Standard Time zone
-- SQL DATEADD function – SQL DATEDIFF function
SELECT CURRENT_TIMESTAMP                        -- 2012-01-05 07:02:10.577
-- SQL Server DATEADD function
SELECT DATEADD(month,2,'2012-12-09')            -- 2013-02-09 00:00:00.000
-- SQL Server DATEDIFF function
SELECT DATEDIFF(day,'2012-12-09','2013-02-09')  -- 62
-- SQL Server DATENAME function
SELECT DATENAME(month,   '2012-12-09')          -- December
SELECT DATENAME(weekday, '2012-12-09')          -- Sunday
-- SQL Server DATEPART function
SELECT DATEPART(month, '2012-12-09')            -- 12
-- SQL Server DAY function
SELECT DAY('2012-12-09')                        -- 9
-- SQL Server GETDATE function
-- local NYC – EST – Eastern Standard Time zone
SELECT GETDATE()                                -- 2012-01-05 07:02:10.577
-- SQL Server GETUTCDATE function
-- London – Greenwich Mean Time
SELECT GETUTCDATE()                             -- 2012-01-05 12:02:10.577
-- SQL Server MONTH function
SELECT MONTH('2012-12-09')                      -- 12
-- SQL Server YEAR function
SELECT YEAR('2012-12-09')                       -- 2012