MS-SQL 날짜함수, 숫자함수, 문자함수, 널함수, 1000단위콤마

-> 네이버로

 

★ 날짜함수 

 

 

 

날짜시간타입을 -->  날짜만의 문자로 변환 ('2010-02-09 13:25:52' 이 화요일일때)

 

 

 

    select convert(char(10), getdate(),121)  --> '2010-02-09'      (121스타일은 밀리세컨드 포함, 120스타일은 초까지만)

 

 

 

    select convert(char(19), getdate(),121)  --> '2010-02-09 13:25:52' 

 

 

 

    select convert(char(8), getdate(),112)   --> '20100209'                   

 

 

 

    select convert(char(8), getdate(),108)   --> '22:46:12'   (24시간체계 시:분:초)

 

 

 

    select datename(dw, getdate())         --> '화요일'    

 

 

 

    select datename(mm, getdate())        --> '02'

 

    select datename(yyyy, getdate())      --> '2010' 

 

  

 

    select datename(dd, getdate())         --> '9'   (이상 '09'가 아니다)

 

    SELECT CONVERT(char(8), GETDATE(), 112) + REPLACE(CONVERT(char(8), GETDATE(), 108), ':','')  -> '20170125133545'

 

 

 

날짜시간타입을 -->  정수로 변환 ('2010-02-09'이 화요일일때)

 

 

 

    select datepart(dw, getdate())        --> 3  (일요일이 1)

 

 

 

    select datepart(month, getdate())   --> 2

 

 

 

    select datepart(mm, getdate())   --> 2

 

 

 

두 날짜 사이의 차이 분단위로 구하기 

 

   @dt1 = '2012-06-29 07:10:00'

 

   @dt2 = '2012-06-29 08:40:00' 일때

 

   

 

   select DATEDIFF(mi, @dt1, @dt2)   --> 90 (분단위)

 

 

 

 

★ 이번달 마지막 날짜 구하기 (특정 일자의 월 마지막 날짜는 GETDATE()대신 특정 일자를 넣는다)

 

 

SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1,GETDATE()),-1);   

 

 

★ 문자열 -> 날짜형 변환

 

SELECT CONVERT (DATE, '2019-01-04')               -- 2019-01-04

     , CONVERT (DATE, '20190104')                     -- 2019-01-04

     , CONVERT (DATETIME, '2019-01-04')             -- 2019-01-04 00:00:00.000

     , CONVERT (DATETIME, '2019-01-04 09:15:52')   -- 2019-01-04 09:15:52.000

   

 

 

 

 

★ 숫자함수

select ROUND(123.5,0) --> 124.0  반올림

 

select FLOOR(123.5)   --> 123   버림

 

 

select CEILING(123.5)  --> 124  올림(소수점이하 올림)

 

 

select CEILING(123.5/10)*10  --> 130  올림(원단위 올림)

 

 

 

 

 

원단위  절사  

 

select FLOOR(123.5/10) *10  --> 120

 

원단위 절상

 

select CEILING(123.5/10)*10 --> 130

 

 

 

문자열이 숫자인지 검사 함수 (숫자면 1 숫자가 아니면 0)

 

ISNUMERIC('123') --> 1 리턴,   ISNUMERIC('ABC') --> 0 리턴

 

 

 

♣ 신용카드 결제금액에서 공급가, 부가세 분리하기

 

    결제금액/1.1 를 반올림한 정수금액을 공급가,

 

    결제금액/11 을 반올림한 정수금액을 부가세로 하면 

 

    공급가 + 부가세= 결제금액이 된다. - ceiling(올림), floor(버림)은  원단위를 정확히 맞추기 어렵다.

 

  

 

    부가세는 11 로 나누면 소수점이 무조건 버려지므로 11.0 으로 나누어야 소수점으로 표현되어 반올림 효과가 난다.

 

    이것을 다시  FLOOR한 것은 결과값을 정수형으로 표현하기 위함이다.

 

  

 

    다음 처럼 ROUND한 다음 FLOOR해야 정수형이 된다.

 

   declare @a  float = 2000    -- 8000

 

   Select FLOOR(ROUND(@a/1.1, 0)) [sup_amt], FLOOR(ROUND(@a/11.0, 0)) [vat_amt]

 

 

 

★ 숫자에 1000단위 콤마(,) 붙이기

 

select replace(convert(varchar,convert(money,123456),1),'.00','') -->123,456

 

 

 

★ 문자함수

 

left, right  : 왼쪽, 오른쪽에서 부터 원하는 만큼 문자읽어오기

 

 

 

substring : 특정 위치에서부터      원하는 만큼 문자읽어오기

 

 

 

select len('정상ab1'), datalength('정상ab1')  -->  5 , 7  반환(len은 문자단이크기, datalength는 byte크기)

 

 

 

SELECT REPLICATE('0', 5 - DATALENGTH('123')) + '123'   --> '00123'  (오라클의 Lpad함수구현이랄까)

 

 

 

REPLACE('abc_d', '_', '')   --> 'abcd'    : 특정문자열을 찾아 다른 문자열로 바꾼다.

 

 

 

LTRIM, RTRIM  --> 왼쪽, 오른쪽 공백문자열을 없앤다.

 

 

 

SPACE --> 공백을 지정한 수만큼 반복

 

 

 

STR : 숫자를 문자로 변환

 

 

 

CHARINDEX : 특정 문자열의 위치를 반환하는 함수

           SELECT CHARINDEX('다', '가나다라마')       -> 3 리턴 (처음부터 찾았을 때)  찾은 위치 처음부터 카운트

           SELECT CHARINDEX('자', '가나다라마')       ->  0 리턴 

           SELECT CHARINDEX('다', '가나다라마', 2 )   -> 3 리턴 (2번째부터 찾았을 때)  찾은 위치 처음부터 카운트

           SELECT CHARINDEX('다', '가나다라마', 4)   ->  0 리턴 (4번째부터 찾았을 때는 없으므로) 

 

-- 특정문자 갯수 구하기

declare @s varchar(10) = 'abca1234'

SELECT LEN(@s) - LEN(REPLACE(@s, 'a', ''))   -> 결과 2   

 

★ ISNUMERIC 함수 (식이 숫자인지 아닌지)

식이 숫자이면 1을 아니면 0을 반환

 

SELECT ISNUMERIC('123'), -- 1 리턴

ISNUMERIC('ABC') -- 0 리턴

 

CONVERT(int, '') 은 0을 리턴하지만 CONVERT(int, 'abb') 는 에러를 발생한다.

 

따라서 컬럼A가 varchar 타입이고 숫자만 주로 입력되고 소트를 할때 숫자형으로 소트를 하고 싶을때

 

다음으로 처리할 수 있다.

 

SELECT CASE WHEN ISNUMERIC(컬럼A) = 1 THEN CONVERT(INT, 컬럼A) ELSE NULL END

 

즉 숫자형으로 변환 가능할 때만 CONVERT 함수를 적용하고 아니면 NULL을 리턴하게 한다.

 

 

★ 널함수

ISNULL(A, '0') : A가 널이면 '0'으로 치환(오라클의 NVL함수)

NULLIF(A, B) : A와 B가 같으면 널(NULL)값 반환

COALESCE(A,B,C,D) : A,B,C,D 중 최초로 널이 아닌값  

 

★ RAND() 함수

11부터 20까지 사이의 정수를 랜덤하게 추출하는 할 경우

declare @from int = 11
declare @to   int = 20

select CONVERT(int, (@to-@from+1) * rand() + @from); 

 

★ 나이 계산

DECLARE @birth  date ='2015-12-28' -- 혹은 varchar(10)
SELECT DATEDIFF(YEAR, @birth, GETDATE())   -- 년나이, 단순 년도 차이
SELECT FLOOR(CONVERT(int, DATEDIFF(day, @birth, GETDATE()))/365.2422)  -- 정확한 만 나이 윤년도 계산된

 

★  숫자 앞에 0 채우기   (0001, 0002 ~)

    TICKET_NO 가 int 형일때  2가지 방법이 있다. 

   1번  SELECT Right('000' + CONVERT(varchar(10), TICKET_NO), 4)
   2번  SELECT Right('000' + LTRIM(STR(TICKET_NO)), 4)

 

 

/******************************************************

-  오라클의 Lpad함수(왼쪽에 '0'이나 특정문자 채우기) 구현

-  ms-sql에서 사용자정의함수(UDF)로 만들기

*****************************************************/

CREATE FUNCTION LPAD(
	@s varchar(255), @n int, @p varchar(255) -- p는 앞에 채울 문자 주로 0
) 
returns varchar(255)
as
BEGIN
return IsNULL(REPLICATE(@p,@n-LEN(@s)),'')+@s
END

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

CREATE FUNCTION ZERO_PAD(            -- LPAD변형 앞자리에 0만채울수 있게
	@s varchar(255), @n int

) 
returns varchar(255)
as
BEGIN
return IsNULL(REPLICATE('0',@n-LEN(@s)),'')+@s
END

/************************************************/