DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to obtain the date only part of a datetime column?

The following Microsoft SQL Server T-SQL user-defined function will zero the time part of a datetime column:

-- T-SQL return only the date part of datetime, set time part to 0
CREATE FUNCTION dbo.DateOnly

               (@Date DATETIME)

RETURNS DATETIME

AS

  BEGIN

    DECLARE  @DateOnly DATETIME

    SELECT @DateOnly = DATEADD(DD, 0, DATEDIFF(DD, 0, @Date))

    RETURN (@DateOnly)

  END

GO

 

SELECT dbo.DateOnly(getdate())

 

-- 2015-03-15 00:00:00.000

 

The new DATE data type is available SQL Server 2008 and on:

 

-- SQL Server 2008 DATE data type

SELECT CONVERT (DATE, getdate())

-- 2015-03-15

------------

 

-- String date only formats

SELECT convert(varchar, getdate(), 101)   -- 03/15/2018     -- MM/DD/YYYY               

SELECT convert(varchar, getdate(), 102)   -- 2018.03.15           

SELECT convert(varchar, getdate(), 103)   -- 15/03/2018

SELECT convert(varchar, getdate(), 104)   -- 15.03.2018

SELECT convert(varchar, getdate(), 105)   -- 15-03-2018

SELECT convert(varchar, getdate(), 106)   -- 15 Mar 2018

SELECT convert(varchar, getdate(), 107)   -- Mar 15, 2018

SELECT convert(varchar, getdate(), 111)   -- 2018/03/15

SELECT convert(varchar, getdate(), 112)   -- 20180315

SELECT convert(varchar, getdate(), 3)     -- 15/03/18       -- MM/DD/YY

SELECT convert(varchar, getdate(), 4)     -- 15.03.18

SELECT convert(varchar, getdate(), 5)     -- 15-03-18

SELECT convert(varchar, getdate(), 6)     -- 15 Mar 18

SELECT convert(varchar, getdate(), 7)     -- Mar 15, 18

SELECT convert(varchar, getdate(), 11)    -- 18/03/15       -- YY/MM/DD

SELECT convert(varchar, getdate(), 12)    -- 180315         -- YYMMDD

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE