Skip to content

Instantly share code, notes, and snippets.

@josheinstein
Last active June 11, 2024 01:37
Show Gist options
  • Save josheinstein/76144a3f8bed40c132bf61f8d87490ad to your computer and use it in GitHub Desktop.
Save josheinstein/76144a3f8bed40c132bf61f8d87490ad to your computer and use it in GitHub Desktop.
Creates various MSSQL scalar functions to get dates relative to the current date.
CREATE FUNCTION [Today]() RETURNS date AS BEGIN RETURN CONVERT(date, GETUTCDATE()) END;
GO
CREATE FUNCTION [Tomorrow]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(day, 1, GETUTCDATE())) END;
GO
CREATE FUNCTION [Yesterday]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(day, -1, GETUTCDATE())) END;
GO
CREATE FUNCTION [ThisMonthStart]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(day, 1-DATEPART(day, GETUTCDATE()), GETUTCDATE())) END;
GO
CREATE FUNCTION [ThisMonthEnd]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(day, -1, DATEADD(month, 1, DATEADD(day, 1-DATEPART(day, GETUTCDATE()), GETUTCDATE())))) END;
GO
CREATE FUNCTION [NextMonthStart]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(month, 1, DATEADD(day, 1-DATEPART(day, GETUTCDATE()), GETUTCDATE()))) END;
GO
CREATE FUNCTION [NextMonthEnd]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(day, -1, DATEADD(month, 2, DATEADD(day, 1-DATEPART(day, GETUTCDATE()), GETUTCDATE())))) END;
GO
CREATE FUNCTION [LastMonthStart]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(month, -1, DATEADD(day, 1-DATEPART(day, GETUTCDATE()), GETUTCDATE()))) END;
GO
CREATE FUNCTION [LastMonthEnd]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(day, -1, DATEADD(day, 1-DATEPART(day, GETUTCDATE()), GETUTCDATE()))) END;
GO
CREATE FUNCTION [ThisWeekStart]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(day, 1-DATEPART(weekday, GETUTCDATE()), GETUTCDATE())) END;
GO
CREATE FUNCTION [ThisWeekEnd]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(day, 6, DATEADD(day, 1-DATEPART(weekday, GETUTCDATE()), GETUTCDATE()))) END;
GO
CREATE FUNCTION [NextWeekStart]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(week, 1, DATEADD(day, 1-DATEPART(weekday, GETUTCDATE()), GETUTCDATE()))) END;
GO
CREATE FUNCTION [NextWeekEnd]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(week, 1, DATEADD(day, 6, DATEADD(day, 1-DATEPART(weekday, GETUTCDATE()), GETUTCDATE())))) END;
GO
CREATE FUNCTION [LastWeekStart]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(week, -1, DATEADD(day, 1-DATEPART(weekday, GETUTCDATE()), GETUTCDATE()))) END;
GO
CREATE FUNCTION [LastWeekEnd]() RETURNS date AS BEGIN RETURN CONVERT(date, DATEADD(week, -1, DATEADD(day, 6, DATEADD(day, 1-DATEPART(weekday, GETUTCDATE()), GETUTCDATE())))) END;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment