꿈꾸는 별나라

//현재 날짜가 몇주차인지.
1.SELECT (DAY(convert(varchar(10),getdate(),120)) + (DATEPART(dw, DATEADD (MONTH, DATEDIFF (MONTH, 0,convert(varchar(10),getdate(),120)), 0)) -1)-1)/7 + 1

2.SELECT DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, convert(varchar(10),getdate(),120)), 0),convert(varchar(10),getdate(),120)) +1;

EX) 달력상 2010-09-13일은 두번째주가 아니라 세번째주로 갖고온다.

      특정 날짜형식 'YYYY-MM-DD' 을 넣고 싶으면 convert(varchar(10),getdate(),120) 이 부분만    값 다르게 넣으면 됨.

결과

3

참고로 오라클에서 2010-09-13 일을 TO_CHAR(SYSDATE,'W') 이런식으로 갖고오면 13일을 두번째 주로 인식함.

//현재 날짜가 무슨 요일인지

select datepart(dw,convert(varchar(10),getdate(),120))

결과

0:일요일 1:월요일....

select to_char(sysdate, 'day') from dual
--결과 : 화요일
;
select to_char(sysdate, 'dy') from dual
--결과 : 화
;
select to_char(sysdate, 'd') from dual
--결과 : 3 (1:일, 2:월, 3:화, 4:수, 5:목, 6:금, 7:토)
;

SELECT  (CASE 

           WHEN DATEPART(dw , regdate) = 1 THEN '일'

WHEN DATEPART(dw , regdate) = 2 THEN '월'

WHEN DATEPART(dw , regdate) = 3 THEN '화'

WHEN DATEPART(dw , regdate) = 4 THEN '수'

WHEN DATEPART(dw , regdate) = 5 THEN '목'

WHEN DATEPART(dw , regdate) = 6 THEN '금'

WHEN DATEPART(dw , regdate) = 7 THEN '토'

END ) AS Week

FROM (

SELECT '2017-06-18'  as regdate

)AA

DISTINCT 와 GROUP BY의 차이

IF NOT EXISTS (SELECT * 

                 FROM hsscm_ocp.dbo.mpi_workorder 

WHERE PLANT = '@@plant'

      AND MATERIAL = '@@material'

      AND INPUT_DATE = '@@inputDate'

      AND OUTPUT_DATE = '@@outputDate')

  INSERT INTO hsscm_ocp.dbo.mpi_workorder (

PLANT

,MATERIAL

,INPUT_DATE

,OUTPUT_DATE

,QTY

,OPERATION

,RESOURCE

,ORDER_NO

,ORDER_DIV

,REMARK

,CREATE_ID

,CREATE_DATE

)VALUES(

'@@plant'

,'@@material'

,'@@inputDate'

,'@@outputDate'

, @@qty

,'@@operation'

,'@@resource'

,'@@orderNo'

,'@@orderDiv'

,'@@remark'

,'@@createId'

,getdate()

);

ELSE 

   UPDATE hsscm_ocp.dbo.mpi_workorder

      SET REMARK = '';

MERGE hsscm_ocp.dbo.mpi_workorder T1

      USING (VALUES(1)) AS Source (Number)

 ON  T1.PLANT = '1140'

      AND T1.MATERIAL = '10000003'

      AND T1.INPUT_DATE = '2016-12-23'

      AND T1.OUTPUT_DATE = '2016-12-23'

   WHEN MATCHED THEN 

     UPDATE 

SET  T1.REMARK = ''

   WHEN NOT MATCHED THEN

     INSERT   (

PLANT

,MATERIAL

,INPUT_DATE

,OUTPUT_DATE

,QTY

,OPERATION

,RESOURCE

,ORDER_NO

,ORDER_DIV

,REMARK

,CREATE_ID

,CREATE_DATE

)VALUES(

'1140'

,'10000003'

,'2016-12-23'

,'2016-12-23'

,'' 

,''

,'S311'

,'000010044747'

,'S'

,''

,'sysadmin'

,getdate()

);