date queries

today

SELECT CONVERT(VARCHAR, GETDATE(), 105)

yesterday

select dateadd(dd,-1, cast(getdate() as date))
select dateadd(day,-1, cast(getdate() as date))
select dateadd(day,-1, getdate())
Select * from DatabaseName.dbo.TableName where
dateadd(d, (datediff(d, 0, DateColumnName)) , 0)
=
dateadd(d, (datediff(d, 0, GetDate() - 1)) , 0)
declare @endDateTime datetime = cast(getdate() + 1 as date)
declare @startDateTime datetime = DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE()))
set @endDateTime= dateadd(second, 86399, @endDateTime)

unsorted

/** Get date and time stamp for today/now: **/
SELECT GETDATE()

/** Get today's date: **/
SELECT CONVERT(date, GETDATE())

/** Select records with today's date **/
/** Example below using an Esri versioned view **/
SELECT [OBJECTID]
      ,[DYETESTNUMBER]
      ,[SCHEDULEDDATE]
      ,[SCHEDULEDTIME]
      ,[TESTSTARTTIME]
      ,[Shape]
  FROM [DyeTest].[dbo].DyeTest_evw
  WHERE SCHEDULEDDATE LIKE CONCAT(CONVERT(date, GETDATE()), '%')
/* *** SQL Server Date Calculations *** */
-- First day of prior month
SELECT DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1-DATEPART(day,GetDate()),GetDate())),0) /*first day of prior month*/     
-- Last month
SELECT CAST(DATEPART(m, GetDate())-1 AS INT) /*last month*/
-- Current year
SELECT CAST(DATEPART(yyyy, GetDate())AS INT) /*current year*/
-- Current day
SELECT CAST(DATEPART(dd, GetDate())AS INT)    /*current day*/
-- First day of current year
SELECT DATEADD(yy, DATEDIFF(yy,0,GetDate()), 0) /* first day of current year*/
-- First day of current month
SELECT DATEADD(mm, DATEDIFF(mm,0,GetDate()), 0) /*first day of current month*/
-- Last day of prior month
SELECT DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,GetDate()  ), 0)) /*last day of prior month*/
-- First day of the quarter
SELECT DATEADD(qq, DATEDIFF(qq,0,GetDate()), 0) /*first day of the quarter*/
-- Midnight for current day
SELECT DATEADD(dd, DATEDIFF(dd,0,GetDate()), 0) /*midnight for current day*/
-- Last day of current month
SELECT DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GetDate()  )+1, 0)) /*last day of current month*/
-- Last day of prior year
SELECT DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,GetDate()  ), 0)) /*last day of prior year*/
-- Last day of current year
SELECT DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,GetDate()  )+1, 0)) /*last day of current year*/
-- First day of prior month
SELECT DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1-DATEPART(day,GetDate()),GetDate())),0) /*first day of prior month*/  
--  sql server - How to get just the date part of getdate()?
--
--  If you are using SQL Server 2008 or later

select convert(date, getdate())

--  Otherwise

select convert(varchar(10), getdate(),120)
DECLARE @DT DATETIME
SET @DT = GETDATE()

SELECT DATEADD(day,6,CONVERT(VARCHAR(10),GETDATE(),120))

SELECT CONVERT(VARCHAR(10),DATEADD(DAY,6,GETDATE()),120)

SELECT CONVERT(CHAR(10),@DT,120) + ' 00:00:01'

SELECT REPLACE(CONVERT(CHAR(10),@DT,120),'-','/') + ' 00:00:01'

SET @DT = '2003-01-22 10:31 PM'
SELECT '0' + CAST(MONTH(@DT) AS CHAR(1)) + '/' + CAST(DAY(@DT) AS 
CHAR(2)) + '/' + CAST(YEAR(@DT) AS CHAR(4))

/*-----------------------------------------------------*/

SELECT YEAR(GETDATE()) AS "Year", MONTH(GETDATE()) AS "Month", DAY(GETDATE()) AS "Day"

Comments