SQL Server DatePart Example

select datepart(quarter,getdate())
–Get Week Day Numerical value, Sunday = 1st day of week
SELECT

 

CASE WHEN datepart(weekday,getdate()) = 1 THEN ‘Sunday’
WHEN datepart(weekday,getdate()) = 2 THEN ‘Monday’
WHEN datepart(weekday,getdate()) = 3 THEN ‘Tuesday’
WHEN datepart(weekday,getdate()) = 4 THEN ‘Wednesday’
WHEN datepart(weekday,getdate()) = 5 THEN ‘Thursday’
WHEN datepart(weekday,getdate()) = 6 THEN ‘Friday’
WHEN datepart(weekday,getdate()) = 7 THEN ‘Saturday’END

DECLARE @Date1 datetime

SET @Date1 = ’01/01/2020′

SELECT

CASE WHEN datepart(weekday,@Date1) = 1 THEN ‘Sunday’
WHEN datepart(weekday,@Date1) = 2 THEN ‘Monday’
WHEN datepart(weekday,@Date1) = 3 THEN ‘Tuesday’
WHEN datepart(weekday,@Date1) = 4 THEN ‘Wednesday’
WHEN datepart(weekday,@Date1) = 5 THEN ‘Thursday’
WHEN datepart(weekday,@Date1) = 6 THEN ‘Friday’
WHEN datepart(weekday,@Date1) = 7 THEN ‘Saturday’END

–Date Format DDD, MMM, DD, YYYY
DECLARE @Date2 datetime

SET @Date2 = ’01/01/2020′

SELECT

CASE WHEN datepart(weekday,@Date2) = 1 THEN ‘Sun’
WHEN datepart(weekday,@Date2) = 2 THEN ‘Mon’
WHEN datepart(weekday,@Date2) = 3 THEN ‘Tue’
WHEN datepart(weekday,@Date2) = 4 THEN ‘Wed’
WHEN datepart(weekday,@Date2) = 5 THEN ‘Thu’
WHEN datepart(weekday,@Date2) = 6 THEN ‘Fri’
WHEN datepart(weekday,@Date2) = 7 THEN ‘Sat’END
+ ‘, ‘ + convert(nvarchar(3),@Date2, 107) + ‘, ‘ + convert(nvarchar(2),@Date2,113) + ‘, ‘ + convert(nvarchar(4),year(@Date2))

select datepart(month,getdate())

select datepart(day,getdate())

select datepart(year,getdate())

select datepart(week,getdate())

select datepart(weekday,getdate())

select datepart(hour,getdate())

select datepart(minute,getdate())

select datepart(second,getdate())

select datepart(millisecond,getdate())

About Greg Owen

Over 27 years programming experience 17 years Oracle SQL/Stored Procedure programming Over 17 years SQL 2000/2005/2008/2012/2016 Server SQL/Stored Procedure programming Over 25 years Visual Basic 3.0/4.0/5.0/6.0/.Net/2003/2005/2008/2010/2015/2019 Over 19 years ASP.Net Over 17 years Visual C# Over 25 years Microsoft Office and VBA HTML JAVA COBOL EASYTRIEVE FORTRAN

‹ Facebook Tip – View Photos The ‘Old’ Way (Without the Pop Up)

Create a Table From a SQL Statement ›

Tagged with: datepart, sql datepart example, sql quick tip, sql server tips, Technology
Posted in SQL Server