Computer/MSSQL

DATETIME 변환

알찬돌삐 2017. 10. 31. 09:51
10 31 2017 9:45AM CONVERT(CHAR(19), CURRENT_TIMESTAMP, 0)
10/31/17CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1)
17.10.31CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2)
31/10/17CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3)
31.10.17CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4)
31-10-17CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5)
31 10 17CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6)
10 31, 17 CONVERT(CHAR(10), CURRENT_TIMESTAMP, 7)
09:45:09CONVERT(CHAR(8), CURRENT_TIMESTAMP, 8)
10 31 2017 9:45:09:990AM CONVERT(CHAR(26), CURRENT_TIMESTAMP, 9)
10-31-17CONVERT(CHAR(8), CURRENT_TIMESTAMP, 10)
17/10/31CONVERT(CHAR(8), CURRENT_TIMESTAMP, 11)
171031 CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12)
31 10 2017 09:45:09:990CONVERT(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.990CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21)
10/31/17 9:45:09 AMCONVERT(CHAR(20), CURRENT_TIMESTAMP, 22)
2017-10-31 CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23)
09:45:09CONVERT(CHAR(8), CURRENT_TIMESTAMP, 24)
2017-10-31 09:45:09.990CONVERT(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, 2017CONVERT(CHAR(12), CURRENT_TIMESTAMP, 107)
09:45:09CONVERT(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)
20171031CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)
31 10 2017 09:45:09:990CONVERT(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.990CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121)
2017-10-31T09:45:09.990CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
2017-10-31T09:45:09.990CONVERT(CHAR(23), CURRENT_TIMESTAMP, 127)
11 ??? 1439 9:45:09:990AMCONVERT(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 -- 전체평균