sql-server - primer - sql server ultimo dia habil del mes

Cómo obtener el primer y último día del mes anterior(con fecha y hora) en SQL Server (11)

Aquí hay una manera bastante directa y dinámica. Para el último día del último mes, reste el día numérico actual de la fecha de hoy. Para el primer día del mes pasado, use el mismo código, simplemente repita restando el día numérico del resultado anterior y agregue 1.

declare @PriorEOM as datetime declare @PriorFOM as datetime Set @PriorEOM = dateadd(day,-day(getdate()),getdate()) Set @PriorFOM = dateadd(day,-day(@PriorEOM) + 1,@PriorEOM) Print @PriorEOM Print @PriorFOM

No pude encontrar la solución que da el primer y último día del mes anterior con fecha y hora. Espero que esto ayude a otros. Si ya hay una solución para este problema, me disculpo.

Aquí está la solución.

SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) as FirtDayPreviousMonthWithTimeStamp, DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) as LastDayPreviousMonthWithTimeStamp

Esto devolverá lo siguiente si currentdate = ''2012-7-31''

resultado: 2012-06-01 00:00:00.000 2012-06-30 23:59:59.000

Esto devolverá lo siguiente si currentdate = ''2012-1-1''

resultado: 2011-12-01 00:00:00.000 2011-12-31 23:59:59.000

Desde SQL2012, hay una nueva función introducida llamada EOMONTH . Usando esta función, el primer y último día del mes se pueden encontrar fácilmente.

select DATEADD(DD,1,EOMONTH(Getdate(),-1)) firstdayofmonth, EOMONTH(Getdate()) lastdayofmonth

He utilizado la siguiente lógica en los informes de SSRS.

BUS_DATE = 17-09-2013 X=DATEADD(MONTH,-1,BUS_DATE) = 17-08-2013 Y=DAY(BUS_DATE)=17 first_date = DATEADD(DAY,-Y+1,X)=01-08-2013 last_date = DATEADD(DAY,-Y,BUS_DATE)=31-08-2013

No he visto esta solución presentada todavía; esta es mi preferencia por su legibilidad más simple:

select dateadd(month,-1,format(getutcdate(),''yyyy-MM-01''))

Primer día de la semana actual.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106)

Último día de la semana actual.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),6),106)

Primer día de la semana pasada.

select CONVERT(varchar,DATEADD(week,datediff(week,7,getdate()),0),106)

Último día de la semana pasada.

select CONVERT(varchar,dateadd(week,datediff(week,7,getdate()),6),106)

Primer día de la próxima semana.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),7),106)

Último día de la próxima semana.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),13),106)

Primer día del mes actual.

select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)

Último día del mes actual.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()))),dateadd(m,1,getdate())),106)

En este ejemplo, la fecha Works on Only es 31. y los días restantes no.

Primer día del último mes.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)

Último día del último mes.

select CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)

Primer día del próximo mes.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()-1))),dateadd(m,1,getdate())),106)

Último día del próximo mes.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,2,getdate()))),DATEADD(m,2,getdate())),106)

Primer día del año actual.

select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106)

Último día del año actual.

select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106)

Primer día del año pasado.

select CONVERT(varchar,dateadd(year,datediff(year,0,getdate())-1,0),106)

Último día del año pasado.

select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate()),0))),106)

Primer día del próximo año.

select CONVERT(varchar,dateadd(YEAR,DATEDIFF(year,0,getdate())+1,0),106)

Último día del próximo año.

select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+2,0))),106)

Puede obtener el primer y último día del mes anterior (con indicación de fecha y hora) en SQL Server ejecutando

--select dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,-1,getdate())) --first day of previous month --select dateadd(dd,-datepart(dd,getdate()),getdate()) -- last day of previous month**

Tome una fecha base que es el 31 de algún mes, por ejemplo, ''20011231''. Luego usa el
siguiente procedimiento (He dado 3 ejemplos idénticos a continuación, solo difiere el valor @dt).

declare @dt datetime; set @dt = ''20140312'' SELECT DATEADD(month, DATEDIFF(month, ''20011231'', @dt), ''20011231''); set @dt = ''20140208'' SELECT DATEADD(month, DATEDIFF(month, ''20011231'', @dt), ''20011231''); set @dt = ''20140405'' SELECT DATEADD(month, DATEDIFF(month, ''20011231'', @dt), ''20011231'');

Solución

El formato de fecha que solicitó se llama formato ODBC (código 120 ).

Para calcular realmente los valores que ha solicitado, incluya lo siguiente en su SQL.

Copiar pegar...

... y usar en tu código:
@FirstDayOfLastMonth
@ LastDayOfLastMonth

Tenga en cuenta que debe pegarse antes que cualquier instrucción que haga referencia a los parámetros, pero a partir de ese momento puede hacer referencia a @FirstDayOfLastMonth y @LastDayOfLastMonth en su código.

Ejemplo

Veamos un código en acción:

DECLARE @FirstDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, -1, GETDATE() - 2)) ), DATEADD(m, -1, GETDATE() - 1))) , @LastDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(GETDATE()) ), GETDATE())) SELECT ''First day of last month'' AS Title, CONVERT(VARCHAR, @FirstDayOfLastMonth , 120) AS [ODBC] UNION SELECT ''Last day of last month'' AS Title, CONVERT(VARCHAR, @LastDayOfLastMonth , 120) AS [ODBC]

Ejecute el código anterior para producir el siguiente resultado:

Nota: Tenga en cuenta que la fecha de hoy para mí es el 12 de septiembre de 2016 .

Más (para completar)

Parámetros de fecha comunes

¿Te dejan más?

Para configurar una gama más completa de parámetros relacionados con fechas útiles, incluya lo siguiente en su SQL:

Tendría más sentido incluirlo antes, preferiblemente en la parte superior de su procedimiento o consulta SQL.

Una vez declarados, los parámetros se pueden referenciar en cualquier parte de su código, tantas veces como los necesite.

Ejemplo

Veamos un código en acción:

DECLARE @FirstDayOfCurrentWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)) , @LastDayOfCurrentWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 6)) , @FirstDayOfLastWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 7, GETDATE()), 0)) , @LastDayOfLastWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 7, GETDATE()), 6)) , @FirstDayOfNextWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 7)) , @LastDayOfNextWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 13)) , @FirstDayOfCurrentMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(GETDATE() - 1) ), GETDATE())) , @LastDayOfCurrentMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 1, GETDATE())) ), DATEADD(m, 1, GETDATE()))) , @FirstDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, -1, GETDATE() - 2)) ), DATEADD(m, -1, GETDATE() - 1))) , @LastDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(GETDATE()) ), GETDATE())) , @FirstDayOfNextMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 1, GETDATE() - 1)) ), DATEADD(m, 1, GETDATE()))) , @LastDayOfNextMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 2, GETDATE())) ), DATEADD(m, 2, GETDATE()))) , @FirstDayOfCurrentYear DATETIME = CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) , @LastDayOfCurrentYear DATETIME = CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)))) , @FirstDayOfLastYear DATETIME = CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) , @LastDayOfLastYear DATETIME = CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)))) , @FirstDayOfNextYear DATETIME = CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) , @LastDayOfNextYear DATETIME = CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 2, 0)))) SELECT ''a) FirstDayOfCurrentWeek.'' AS [Title] , @FirstDayOfCurrentWeek AS [DATE (Server default)] , CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 127) AS [ISO8601] , CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 100) AS [U.S.] , CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 120) AS [ODBC] UNION SELECT ''b) LastDayOfCurrentWeek.'' AS [Title] , @LastDayOfCurrentWeek AS [DATE (Server default)] , CONVERT(VARCHAR, @LastDayOfCurrentWeek, 127) AS [ISO8601] , CONVERT(VARCHAR, @LastDayOfCurrentWeek, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @LastDayOfCurrentWeek, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @LastDayOfCurrentWeek, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @LastDayOfCurrentWeek, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @LastDayOfCurrentWeek, 100) AS [U.S.] , CONVERT(VARCHAR, @LastDayOfCurrentWeek, 120) AS [ODBC] UNION SELECT ''c) FirstDayOfLastWeek.'' AS [Title] , @FirstDayOfLastWeek AS [DATE (Server default)] , CONVERT(VARCHAR, @FirstDayOfLastWeek, 127) AS [ISO8601] , CONVERT(VARCHAR, @FirstDayOfLastWeek, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @FirstDayOfLastWeek, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @FirstDayOfLastWeek, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @FirstDayOfLastWeek, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @FirstDayOfLastWeek, 100) AS [U.S.] , CONVERT(VARCHAR, @FirstDayOfLastWeek, 120) AS [ODBC] UNION SELECT ''d) LastDayOfLastWeek.'' AS [Title] , @LastDayOfLastWeek AS [DATE (Server default)] , CONVERT(VARCHAR, @LastDayOfLastWeek, 127) AS [ISO8601] , CONVERT(VARCHAR, @LastDayOfLastWeek, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @LastDayOfLastWeek, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @LastDayOfLastWeek, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @LastDayOfLastWeek, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @LastDayOfLastWeek, 100) AS [U.S.] , CONVERT(VARCHAR, @LastDayOfLastWeek, 120) AS [ODBC] UNION SELECT ''e) FirstDayOfNextWeek.'' AS [Title] , @FirstDayOfNextWeek AS [DATE (Server default)] , CONVERT(VARCHAR, @FirstDayOfNextWeek, 127) AS [ISO8601] , CONVERT(VARCHAR, @FirstDayOfNextWeek, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @FirstDayOfNextWeek, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @FirstDayOfNextWeek, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @FirstDayOfNextWeek, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @FirstDayOfNextWeek, 100) AS [U.S.] , CONVERT(VARCHAR, @FirstDayOfNextWeek, 120) AS [ODBC] UNION SELECT ''f) LastDayOfNextWeek.'' AS [Title] , @LastDayOfNextWeek AS [DATE (Server default)] , CONVERT(VARCHAR, @LastDayOfNextWeek, 127) AS [ISO8601] , CONVERT(VARCHAR, @LastDayOfNextWeek, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @LastDayOfNextWeek, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @LastDayOfNextWeek, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @LastDayOfNextWeek, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @LastDayOfNextWeek, 100) AS [U.S.] , CONVERT(VARCHAR, @LastDayOfNextWeek, 120) AS [ODBC] UNION SELECT ''g) FirstDayOfCurrentMonth.'' AS [Title] , @FirstDayOfCurrentMonth AS [DATE (Server default)] , CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 127) AS [ISO8601] , CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 100) AS [U.S.] , CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 120) AS [ODBC] UNION SELECT ''h) LastDayOfCurrentMonth.'' AS [Title] , @LastDayOfCurrentMonth AS [DATE (Server default)] , CONVERT(VARCHAR, @LastDayOfCurrentMonth, 127) AS [ISO8601] , CONVERT(VARCHAR, @LastDayOfCurrentMonth, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @LastDayOfCurrentMonth, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @LastDayOfCurrentMonth, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @LastDayOfCurrentMonth, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @LastDayOfCurrentMonth, 100) AS [U.S.] , CONVERT(VARCHAR, @LastDayOfCurrentMonth, 120) AS [ODBC] UNION SELECT ''i) FirstDayOfLastMonth.'' AS [Title] , @FirstDayOfLastMonth AS [DATE (Server default)] , CONVERT(VARCHAR, @FirstDayOfLastMonth, 127) AS [ISO8601] , CONVERT(VARCHAR, @FirstDayOfLastMonth, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @FirstDayOfLastMonth, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @FirstDayOfLastMonth, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @FirstDayOfLastMonth, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @FirstDayOfLastMonth, 100) AS [U.S.] , CONVERT(VARCHAR, @FirstDayOfLastMonth, 120) AS [ODBC] UNION SELECT ''j) LastDayOfLastMonth.'' AS [Title] , @LastDayOfLastMonth AS [DATE (Server default)] , CONVERT(VARCHAR, @LastDayOfLastMonth, 127) AS [ISO8601] , CONVERT(VARCHAR, @LastDayOfLastMonth, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @LastDayOfLastMonth, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @LastDayOfLastMonth, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @LastDayOfLastMonth, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @LastDayOfLastMonth, 100) AS [U.S.] , CONVERT(VARCHAR, @LastDayOfLastMonth, 120) AS [ODBC] UNION SELECT ''k) FirstDayOfNextMonth.'' AS [Title] , @FirstDayOfNextMonth AS [DATE (Server default)] , CONVERT(VARCHAR, @FirstDayOfNextMonth, 127) AS [ISO8601] , CONVERT(VARCHAR, @FirstDayOfNextMonth, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @FirstDayOfNextMonth, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @FirstDayOfNextMonth, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @FirstDayOfNextMonth, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @FirstDayOfNextMonth, 100) AS [U.S.] , CONVERT(VARCHAR, @FirstDayOfNextMonth, 120) AS [ODBC] UNION SELECT ''l) LastDayOfNextMonth.'' AS [Title] , @LastDayOfNextMonth AS [DATE (Server default)] , CONVERT(VARCHAR, @LastDayOfNextMonth, 127) AS [ISO8601] , CONVERT(VARCHAR, @LastDayOfNextMonth, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @LastDayOfNextMonth, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @LastDayOfNextMonth, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @LastDayOfNextMonth, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @LastDayOfNextMonth, 100) AS [U.S.] , CONVERT(VARCHAR, @LastDayOfNextMonth, 120) AS [ODBC] UNION SELECT ''m) FirstDayOfCurrentYear.'' AS [Title] , @FirstDayOfCurrentYear AS [DATE (Server default)] , CONVERT(VARCHAR, @FirstDayOfCurrentYear, 127) AS [ISO8601] , CONVERT(VARCHAR, @FirstDayOfCurrentYear, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @FirstDayOfCurrentYear, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @FirstDayOfCurrentYear, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @FirstDayOfCurrentYear, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @FirstDayOfCurrentYear, 100) AS [U.S.] , CONVERT(VARCHAR, @FirstDayOfCurrentYear, 120) AS [ODBC] UNION SELECT ''n) LastDayOfCurrentYear.'' AS [Title] , @LastDayOfCurrentYear AS [DATE (Server default)] , CONVERT(VARCHAR, @LastDayOfCurrentYear, 127) AS [ISO8601] , CONVERT(VARCHAR, @LastDayOfCurrentYear, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @LastDayOfCurrentYear, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @LastDayOfCurrentYear, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @LastDayOfCurrentYear, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @LastDayOfCurrentYear, 100) AS [U.S.] , CONVERT(VARCHAR, @LastDayOfCurrentYear, 120) AS [ODBC] UNION SELECT ''o) FirstDayOfLastYear.'' AS [Title] , @FirstDayOfLastYear AS [DATE (Server default)] , CONVERT(VARCHAR, @FirstDayOfLastYear, 127) AS [ISO8601] , CONVERT(VARCHAR, @FirstDayOfLastYear, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @FirstDayOfLastYear, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @FirstDayOfLastYear, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @FirstDayOfLastYear, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @FirstDayOfLastYear, 100) AS [U.S.] , CONVERT(VARCHAR, @FirstDayOfLastYear, 120) AS [ODBC] UNION SELECT ''p) LastDayOfLastYear.'' AS [Title] , @LastDayOfLastYear AS [DATE (Server default)] , CONVERT(VARCHAR, @LastDayOfLastYear, 127) AS [ISO8601] , CONVERT(VARCHAR, @LastDayOfLastYear, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @LastDayOfLastYear, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @LastDayOfLastYear, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @LastDayOfLastYear, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @LastDayOfLastYear, 100) AS [U.S.] , CONVERT(VARCHAR, @LastDayOfLastYear, 120) AS [ODBC] UNION SELECT ''q) FirstDayOfNextYear.'' AS [Title] , @FirstDayOfNextYear AS [DATE (Server default)] , CONVERT(VARCHAR, @FirstDayOfNextYear, 127) AS [ISO8601] , CONVERT(VARCHAR, @FirstDayOfNextYear, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @FirstDayOfNextYear, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @FirstDayOfNextYear, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @FirstDayOfNextYear, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @FirstDayOfNextYear, 100) AS [U.S.] , CONVERT(VARCHAR, @FirstDayOfNextYear, 120) AS [ODBC] UNION SELECT ''r) LastDayOfNextYear.'' AS [Title] , @LastDayOfNextYear AS [DATE (Server default)] , CONVERT(VARCHAR, @LastDayOfNextYear, 127) AS [ISO8601] , CONVERT(VARCHAR, @LastDayOfNextYear, 103) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] , CONVERT(VARCHAR, @LastDayOfNextYear, 104) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] , CONVERT(VARCHAR, @LastDayOfNextYear, 105) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] , CONVERT(VARCHAR, @LastDayOfNextYear, 111) + '' '' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] , CONVERT(VARCHAR, @LastDayOfNextYear, 100) AS [U.S.] , CONVERT(VARCHAR, @LastDayOfNextYear, 120) AS [ODBC];

Ejecute el código anterior para producir el siguiente resultado:

Si su país falta, es porque no conozco el código. Sería de gran ayuda y apreciaría que pudiera editar esta respuesta y agregar una nueva columna para su país.

Gracias por adelantado.

Nota: Tenga en cuenta que la fecha de hoy para mí es el 12 de septiembre de 2016 .

Referencias

Para leer más sobre el estándar de fecha internacional ISO8601, siga este enlace:

https://en.wikipedia.org/wiki/ISO_8601

Para leer más sobre el estándar de fecha internacional ODBC, siga este enlace:

https://technet.microsoft.com/en-us/library/ms190234(v=sql.90).aspx

Para ver la lista de formatos de fecha desde la que trabajé, siga este enlace:

https://msdn.microsoft.com/en-GB/library/ms187928.aspx

Para obtener más información sobre el tipo de datos DATETIME, siga este enlace:

https://msdn.microsoft.com/en-GB/library/ms187819.aspx

SELECT CONVERT(DATE,DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-1, 0)) AS FirstDayOfPrevMonth SELECT CONVERT(DATE,DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) , 0))) AS LastDayOfPrevMonth

Para más detalles codefari.com/2014/12/how-to-get-first-last-day-of-previous.html

SELECT DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0) AS PreviousMonthStart SELECT DATEADD(ms,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) AS PreviousMonthEnd

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) --First day of previous month select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) --Last Day of previous month