- CREATE FUNCTION [dbo].[GetLastDayOfMonth](@Dte DATETIME, @AtMidnight BIT)
- RETURNS DATETIME
- /* Retourne la date du dernier jour du mois avec ou sans gestion de l'heure.
- Exemples d'utilisation :
- Select dbo.GetLastDayOfMonth(Cast('05/02/2007 12:20:49.250' As DateTime), 1) >> 2002-02-28 00:00:00.000
- Select dbo.GetLastDayOfMonth(Cast('05/02/2007 12:20:49.250' As DateTime), 0) >> 2007-02-28 23:59:59.997
- Select dbo.GetLastDayOfMonth(Cast('05/02/2007 12:20:49.250' As DateTime), Null) >> 2007-02-28 12:20:49.250
- */
- BEGIN
- IF @AtMidnight= 0
- RETURN DATEADD(MILLISECOND, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Dte) + 1, 0))
- ELSE
- SET @Dte=DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(@Dte), @Dte)));
-
- IF @AtMidnight= 1
- RETURN CAST(CONVERT(CHAR(10), @Dte, 103) AS DATETIME);
-
- RETURN @Dte;
- END
-
- CREATE FUNCTION [dbo].[GetLastDay](@Dte DATETIME)
- RETURNS DATETIME
- /* Retourne la date du dernier jour du mois sans gestion de l'heure.
- Exemples d'utilisation (tests de validation) :
- Select dbo.GetLastDayOfMonth(Cast('01/02/2007 12:20:49.250' As DateTime))>>2007-02-28 12:20:49.250
- Select dbo.GetLastDayOfMonth(Cast('10/02/2007 12:20:49.250' As DateTime))>>2007-02-28 12:20:49.250
- Select dbo.GetLastDayOfMonth(Cast('28/02/2007 12:20:49.250' As DateTime))>>2007-02-28 12:20:49.250
- */
- BEGIN
- SET @Dte=DATEADD(MONTH, 1, @Dte)
- RETURN DATEADD(DAY, -DAY(@Dte), @Dte)
- END
CREATE FUNCTION [dbo].[GetLastDayOfMonth](@Dte DATETIME, @AtMidnight BIT)
RETURNS DATETIME
/* Retourne la date du dernier jour du mois avec ou sans gestion de l'heure.
Exemples d'utilisation :
Select dbo.GetLastDayOfMonth(Cast('05/02/2007 12:20:49.250' As DateTime), 1) >> 2002-02-28 00:00:00.000
Select dbo.GetLastDayOfMonth(Cast('05/02/2007 12:20:49.250' As DateTime), 0) >> 2007-02-28 23:59:59.997
Select dbo.GetLastDayOfMonth(Cast('05/02/2007 12:20:49.250' As DateTime), Null) >> 2007-02-28 12:20:49.250
*/
BEGIN
IF @AtMidnight= 0
RETURN DATEADD(MILLISECOND, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Dte) + 1, 0))
ELSE
SET @Dte=DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(@Dte), @Dte)));
IF @AtMidnight= 1
RETURN CAST(CONVERT(CHAR(10), @Dte, 103) AS DATETIME);
RETURN @Dte;
END
CREATE FUNCTION [dbo].[GetLastDay](@Dte DATETIME)
RETURNS DATETIME
/* Retourne la date du dernier jour du mois sans gestion de l'heure.
Exemples d'utilisation (tests de validation) :
Select dbo.GetLastDayOfMonth(Cast('01/02/2007 12:20:49.250' As DateTime))>>2007-02-28 12:20:49.250
Select dbo.GetLastDayOfMonth(Cast('10/02/2007 12:20:49.250' As DateTime))>>2007-02-28 12:20:49.250
Select dbo.GetLastDayOfMonth(Cast('28/02/2007 12:20:49.250' As DateTime))>>2007-02-28 12:20:49.250
*/
BEGIN
SET @Dte=DATEADD(MONTH, 1, @Dte)
RETURN DATEADD(DAY, -DAY(@Dte), @Dte)
END