DATETIME 변환

10 31 2017 9:45AM	CONVERT(CHAR(19), CURRENT_TIMESTAMP, 0)
10/31/17	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1)
17.10.31	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2)
31/10/17	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3)
31.10.17	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4)
31-10-17	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5)
31 10 17	CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6)
10 31, 17	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 7)
09:45:09	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 8)
10 31 2017 9:45:09:990AM	CONVERT(CHAR(26), CURRENT_TIMESTAMP, 9)
10-31-17	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 10)
17/10/31	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 11)
171031	CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12)
31 10 2017 09:45:09:990	CONVERT(CHAR(24), CURRENT_TIMESTAMP, 13)
09:45:09:990	CONVERT(CHAR(12), CURRENT_TIMESTAMP, 14)
2017-10-31 09:45:09	CONVERT(CHAR(19), CURRENT_TIMESTAMP, 20)
2017-10-31 09:45:09.990	CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21)
10/31/17 9:45:09 AM	CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22)
2017-10-31	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23)
09:45:09	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 24)
2017-10-31 09:45:09.990	CONVERT(CHAR(23), CURRENT_TIMESTAMP, 25)
10 31 2017 9:45AM	CONVERT(CHAR(19), CURRENT_TIMESTAMP, 100)
10/31/2017	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 101)
2017.10.31	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102)
31/10/2017	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103)
31.10.2017	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 104)
31-10-2017	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 105)
31 10 2017	CONVERT(CHAR(11), CURRENT_TIMESTAMP, 106)
10 31, 2017	CONVERT(CHAR(12), CURRENT_TIMESTAMP, 107)
09:45:09	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108)
10 31 2017 9:45:09:990AM	CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109)
10-31-2017	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 110)
2017/10/31	CONVERT(CHAR(10), CURRENT_TIMESTAMP, 111)
20171031	CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)
31 10 2017 09:45:09:990	CONVERT(CHAR(24), CURRENT_TIMESTAMP, 113)
09:45:09:990	CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114)
2017-10-31 09:45:09	CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120)
2017-10-31 09:45:09.990	CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121)
2017-10-31T09:45:09.990	CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
2017-10-31T09:45:09.990	CONVERT(CHAR(23), CURRENT_TIMESTAMP, 127)
11 ??? 1439 9:45:09:990AM	CONVERT(CHAR(32), CURRENT_TIMESTAMP, 130)
11/02/1439 9:45:09:990AM	CONVERT(CHAR(25), CURRENT_TIMESTAMP, 131)

--------------------------------------------------------------------------------------------------

출처 : http://syk.kr/trackback/97

/* 테이블의 칼럼정보 보기( Oracle 의 desc 테이블명 ) */ 

exec sp_columns sales 

/* Substring 처리 */ 

select payterms from sales 

select substring(payterms,1,3) from sales 

/* 문자열연결 연산자 '+' */ 

select substring(payterms,1,4)+ ' 테스트' from sales 

/*현재날짜 */ 

select getdate() 

결과 : 2002-05-09 10:51:45.870 

/* 1년후날짜 */ 

select getdate() + 365 

결과 : 2003-05-09 10:50:43.020 

/* 날짜데이타 변경 CONVERT(변경될문자열의 데이타타입및길이, 날짜데이타, 출력형식) */ 

select convert(varchar(30), getdate(), 2) 

결과 : 02.05.09 

select convert(varchar(30), getdate(), 102) 

결과 : 2002.05.09 

/* YYYYMMDD 형식으로 가져오기( Varchar(8)로 설정 ) */ 

select convert(varchar(8), getdate(), 112) 

결과 : 20020509 

/* 해당 [월] 만 가져오기 */ 

select datepart(mm,getdate()) 

select month(getdate()) 

결과 : 5 

/* 현재일자에 20개월추가 (월 추가) */ 

select dateadd(mm,20,getdate()) 

결과 : 2004-01-09 10:56:57.580 

/* 현재일자에 100일 후의 날짜 */ 

select dateadd(dd,100,getdate()) 

/* Oracle 에서의 NVL함수에 대응하는 MS-SQL함수 */ 

NVL(price,0) ===> ISNULL(price,0) 

/* 판매수량이 30권 이상인책에 대하여 각 서적별 판매수량총계 조회 */ 

select title_id as '서적코드' , sum(qty) as '판매수량' from sales 

group by title_id 

having sum(qty) >= 30 

/* COMPUTE & COMPUTE BY 사용법 */ 

select type, title_id, price from titles 

order by type 

compute avg(price) by type 

해당 select칼럼이 별로 Average값을 구해준다. ( Order by 반드시 필요 ) 

select type, title_id, price from titles 

order by type 

compute avg(price) by type 

마지막행에 Select된 전체칼럼중 Average(price)칼럼에 대해 평균값을 구해준다. 

/* Rollup & Cube */ 

select type, avg(price) from titles group by type --각 타입별 평균 

select avg(price) from titles -- 전체평균