Search

Advertisements

Showing posts with label Date -Time data types and functions. Show all posts
Showing posts with label Date -Time data types and functions. Show all posts

SQL Server - Calender details

DECLARE @StartDate DATE = '2014-01-01', @EndDate DATE = '2014-12-31'
;With CTE
AS
(
SELECT @StartDate dt
UNION ALL
SELECT DATEADD(DAY, 1, dt) FROM Cte
WHERE dt < @EndDate
)
SELECT
FORMAT ( dt, 'D', 'en-US' ) Calender
,DATENAME(dayofyear, dt) DayNumber_Year
,DATEPART(day, dt) DayNumber_Month
,DATEPART(weekday, dt) DayNumber_Week
,DATENAME(weekday, dt) DayName
,DATEPART(WEEK,dt) WeekNumber
,IIF ( DATEPART(weekday, dt) IN (1,7) , 'Weekend', 'Weekday' ) Day
,DATENAME(month, dt) MonthName
,DATEPART(month, dt) MonthNumber
,DATENAME(year, dt) Year
,EOMONTH (dt) MonthLastDate
FROM cte
OPTION (MAXRECURSION 366);


 

SQL Server - SET DATEFIRST and @@DATEFIRST

SELECT * FROM sys.syslanguages

Above query will return resultset(date & language details) as shown in below image:


--@@LANGUAGE will give us the language detail for the current session
--@@DATEFIRST will give us the "first day of a week" value for the current session
--for language 'us_english', default value (first day of a week) = 7

 
SELECT @@LANGUAGE Currentsession_Language
SELECT @@DATEFIRST Defaultvalue_for_usenglish

--to override the default value (first day of a week) = 6 for this current session alone,use SET DATEFIRST

 
SET DATEFIRST 6
SELECT @@DATEFIRST Overriddenvalue_first_day_of_week 



Advertisements