본문 바로가기
프로그래밍/DBMS

MSSQL, 줄리안 데이트로 바꾸기

by zoo10 2012. 1. 2.

이달말일

SELECT 
(
	DatePART
	(
		yy,
		(
			DATEADD(day, -1, DATEADD
								( mm, 1, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)) )
		)
	) -1900
) * 1000 
+
DatePART
(
	dy, 
	(
		DATEADD
		(
			day, 
			-1, 
			DATEADD(mm, 1, DATEADD
							(mm, DATEDIFF(mm, 0, getdate()), 0)
			)
		)
	)
)

전달말일

SELECT
(
    DatePART
    (
        yy,
        (
            DATEADD(day, 
					-1, 
					DATEADD
                    ( mm, 0, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)) )
        )
    ) - 1900
) * 1000 
+
DatePART
(
    dy, 
    (
        DATEADD
        (
            day, 
            -1, 
            DATEADD(mm, 0, DATEADD
                            (mm, 
                            DATEDIFF(mm, 0, getdate()), 0)
            )
        )
    )
)--"전달말일"

전전달말일

SELECT 
(
	DatePART
	(
		yy,
		(
			DATEADD(day, -1, DATEADD
								(mm, -1, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)) )
		)
	) - 1900
) * 1000 
+
DatePART
(
	dy, 
	(
		DATEADD
		(
			day, 
			-1, 
			DATEADD(mm, -1, DATEADD
									(mm, 
									DATEDIFF(mm, 0, getdate()), 0)
			)
		)
	)
)--"전전달말일"

오늘

SELECT (DatePART(yy,getDate())  -1900) *1000 
+
DatePART(dy, getDate()) AS "금일"

특정일

SELECT (DatePART(yy,'20101210')  -1900) *1000 
+
DatePART(dy, '20101210')AS "특정일"

줄리안데이트YYYYMMDD

SELECT 
CONVERT(
	CHAR(8),
	Dateadd(dy,
			110147-convert(int,110147/1000)*1000-1,
			convert(Datetime, 
					convert(char(8), (convert(int,(110147/1000))+1900)*10000+0101),112)),
					112)
AS "줄리안데이트YYYYMMDD"