[sql] 특정 문자만 추출, 월초, 월말 집계 방법, 스케쥴러 조회

 

 

 

--- mysql 임

select	*
from	OPENQUERY([KOR_LOGDB],	
'SELECT	*
FROM	dbLog.Login
WHERE	time >=''2019-02-27 00:00:00'' and time <''2019-02-28 00:00:00''
AND	DevicePlatform=''Android'' 	
AND	replace(SUBSTRING_INDEX(deviceOS, "+" ,3), "Android+OS+", '' '') between 4.0 and 4.2    
limit	10 ;
')

--- SUBSTRING_INDEX 특정문자 건너뛰고 다음 문자열 추출
--- replace 해당문자열 바꾸기

 

 

--- 첫일
SELECT CONVERT(CHAR(10), Dateadd(month, DATEDIFF(MONTH, 0, '2022-09-05'), 0), 120)

--- 마지막일
SELECT   CONVERT(CHAR(10), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'2022-09-04')+1,0)), 120)

 

 

 

--- 스케줄러 조회

SELECT
	A.job_id, A.name, A.enabled , description, A.date_created, A.date_modified , 
	D.step_id, D.step_name, D.subsystem, D.command 
FROM msdb.dbo.sysjobs A 
	INNER JOIN msdb.dbo.sysjobschedules B ON A.job_id = B.job_id 
	INNER JOIN msdb.dbo.sysschedules C ON B.schedule_id = C.schedule_id 
	INNER JOIN msdb.dbo.sysjobsteps D ON A.job_id = D.job_id 
ORDER BY A.name