programing

SQL Server에서 전달의 첫 번째 날과 마지막 날(타임스탬프 포함)을 가져오는 방법

muds 2023. 4. 21. 21:32
반응형

SQL Server에서 전달의 첫 번째 날과 마지막 날(타임스탬프 포함)을 가져오는 방법

지난달 첫 날과 마지막 날을 타임스탬프와 함께 제공하는 솔루션을 찾을 수 없었습니다.

여기 해결책이 있습니다.

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

그러면 다음과 같은 결과가 반환됩니다.currentdate = '2012-7-31'

결과:2012-06-01 00:00:00.000 2012-06-30 23:59:59.000

그러면 다음과 같은 결과가 반환됩니다.currentdate = '2012-1-1'

결과:2011-12-01 00:00:00.000 2011-12-31 23:59:59.000

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

현재 주의 첫째 날

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

현재 주의 마지막 요일

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

지난주 첫날이요

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

지난주 마지막 날

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

다음 주 첫째 날.

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

다음 주 마지막 날.

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

당월 첫째 날

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

현재 달의 마지막 날.

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

이 예에서는 Works only date는 31일이며, 나머지 일수는 31일이 아닙니다.

지난달 첫째 날.

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

지난달 마지막 날

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

다음 달 첫째 날

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

다음 달 마지막 날

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

금년의 첫날

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

금년의 마지막 날

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

작년의 첫날

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

작년 마지막 날

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

내년 1월 1일.

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

내년 마지막 날.

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

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

솔루션

요청한 날짜 형식을 ODBC 형식(코드 120)이라고 합니다.

요청한 값을 실제로 계산하려면 SQL에 다음을 포함합니다.

복사, 붙여넣기...

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()))

...코드에 다음과 같이 입력합니다.

  • @지난 달 첫날
  • @Last Day Of Last Month

이 파라미터는 파라미터를 참조하는 어떤 스테이트먼트보다 빨리 붙여넣을 필요가 있다는 점에 주의해 주십시오.단, 이 시점부터 코드로 @FirstDayOfLastMonth 및 @LastDayOfLastMonth를 참조할 수 있습니다.

예

동작하고 있는 코드를 확인합니다.

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]

위의 코드를 실행하여 다음 출력을 생성합니다.

그림 a

주의: 오늘이 2016년 9월 12일이라는 것을 명심하세요.

상세(완전성을 위해)

공통 날짜 파라미터

당신은 더 필요한 것이 있습니까?

보다 포괄적인 범위의 편리한 날짜 관련 매개 변수를 설정하려면 SQL에 다음을 포함합니다.

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))))

가급적 절차 또는 SQL 쿼리의 맨 위에 포함시키는 것이 가장 적합합니다.

선언된 파라미터는 필요한 횟수만큼 코드의 임의의 장소에서 참조할 수 있습니다.

예

동작하고 있는 코드를 확인합니다.

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];

위의 코드를 실행하여 다음 출력을 생성합니다.

여기에 이미지 설명 입력

만약 당신의 나라가 없어진다면, 그것은 내가 그 나라의 코드를 모르기 때문입니다.이 답변을 편집하여 해당 국가를 위한 새로운 칼럼을 추가해 주시면 감사하겠습니다.

잘 부탁드립니다.

주의: 오늘이 2016년 9월 12일이라는 것을 명심하세요.

레퍼런스

ISO8601 국제 날짜 표준에 대한 자세한 내용은 다음 링크를 참조하십시오.

ODBC 국제 날짜 표준에 대한 자세한 내용은 다음 링크를 참조하십시오.

작업한 날짜 형식의 목록을 보려면 다음 링크를 클릭하십시오.

DATETIME 데이터 유형에 대한 자세한 내용은 다음 링크를 참조하십시오.

SQL2012라는 새로운 되었습니다.EOMONTH·이 기능을 사용하면, 지난달의 첫날과 마지막 날을 간단하게 찾을 수 있습니다.

select DATEADD(DD,1,EOMONTH(Getdate(),-2)) firstdayoflastmonth, EOMONTH(Getdate(), -1) lastdayoflastmonth

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) LastMonthFirstDate

지난달 마지막 날짜 가져오기

select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) LastMonthEndDate

저는 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

SQL Server에서 (타임스탬프와 함께) 이전 달의 첫 번째 날과 마지막 날을 가져올 수 있습니다.

--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**

'20011231'입니다., 그럼 이렇게 하세요.
다음 절차(아래 3개의 동일한 예를 제시했지만 @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');

여기 꽤 직설적이고 역동적인 방법이 있습니다.지난달 마지막 날에 대해 오늘 날짜에서 현재 숫자 날짜를 뺍니다.지난달 첫째 날에도 같은 코드를 사용하여 이전 결과에서 숫자 날짜를 뺀 후 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

아직 이 솔루션을 본 적이 없습니다.간단한 가독성을 원합니다.

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

언급URL : https://stackoverflow.com/questions/11743810/how-to-get-first-and-last-day-of-previous-month-with-timestamp-in-sql-server

반응형

'programing' 카테고리의 다른 글

Apache POI와 함께 날짜가 있는 Excel 셀을 읽는 방법  (0) 2023.04.21
TSQL에서의 COALESCE 기능  (0) 2023.04.21
사용자 매핑 섹션이 열려 있는 파일에서는 요청된 작업을 수행할 수 없습니다.  (0) 2023.04.21
이 저장소에서 다른 Git 프로세스가 실행되고 있는 것 같습니다.  (0) 2023.04.21
bash에게 회선이 다음 줄에 계속됨을 알리는 방법  (0) 2023.04.21

'programing'의 다른글

  • 현재글SQL Server에서 전달의 첫 번째 날과 마지막 날(타임스탬프 포함)을 가져오는 방법

관련글

댓글

티스토리툴바