메뉴 닫기

MS-SQL 날짜 Format

★ 날짜함수
 
날짜시간타입을 –>  날짜만의 문자로 변환 (‘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 ROUND(123.5,0) –> 124.0  반올림
 
select FLOOR(123.5)   –> 123   버림
 
select CEILING(123.5)  –> 124  올림
 
원단위  절사
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

★ 널함수

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  varchar(10) =’2015-12-28′
SELECT DATEDIFF(YEAR, @birth, GETDATE())   — 년나이, 단순 년도 차이
SELECT FLOOR(CONVERT(integer, 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
/************************************************/

33 Comments

  1. cbd oil that works 2020

    Hi there, just became alert to your blog through Google, and found that it is truly informative.
    I am gonna watch out for brussels. I’ll be grateful if you continue this in future.
    Many people will be benefited from your writing. Cheers!

  2. Faustino Fontanilla

    I simply want to say I am just very new to blogging and site-building and seriously savored this web page. More than likely I’m want to bookmark your blog post . You absolutely come with terrific posts. Many thanks for sharing your webpage.

  3. CBD Cream

    I really like your article. It’s evident that you have a lot knowledge on this topic. Your points are well made and relatable. Thanks for writing engaging and interesting material.

  4. Security Screen Masters

    Please let me know if you’re looking for a author for your weblog. You have some really good articles and I believe I would be a good asset. If you ever want to take some of the load off, I’d absolutely love to write some material for your blog in exchange for a link back to mine. Please shoot me an email if interested. Kudos!

  5. discover here

    When I originally commented I clicked the -Alert me when new comments are included- checkbox as well as now each time a comment is added I get 4 emails with the very same comment. Is there any way you can eliminate me from that solution? Thanks!

  6. Tractor Workshop Manuals

    It’s a really amazing powerful resource that you’re offering and you simply provide it away cost-free!! I that can compare with discovering websites which are aware of the particular in providing you with fantastic learning resource for zero cost. We truly dearly loved examining this article. Love!

  7. cbd capsules health benefits

    Youre so cool! I don’t intend Ive check out anything such as this before. So nice to locate somebody with some original ideas on this subject. realy thank you for beginning this up. this site is something that is required online, a person with a little originality. useful task for bringing something brand-new to the net!

  8. Google

    Hi, I do believe this is an excellent site. I stumbledupon it 😉 I’m going to come back yet again since i have saved as a favorite it. Money and freedom is the best way to change, may you be rich and continue to help other people.

  9. web hosting providers

    I’ve been exploring for a little for any high-quality
    articles or blog posts on this sort of house . Exploring in Yahoo
    I finally stumbled upon this site. Reading this
    information So i am satisfied to convey that
    I have an incredibly good uncanny feeling I found out just what I needed.
    I most indubitably will make sure to don?t forget this web site and give it
    a glance on a continuing basis.

  10. my latest blog post

    When I originally commented I clicked the -Notify me when brand-new comments are included- checkbox and also currently each time a remark is added I get 4 emails with the same comment. Exists any way you can eliminate me from that solution? Thanks!

  11. the advantage

    Can I just claim what a relief to discover somebody who really knows what theyre talking about online. You certainly understand how to bring a concern to light and also make it vital. More individuals need to read this and comprehend this side of the story. I cant think youre not much more prominent due to the fact that you definitely have the present.

  12. funny post

    I?d have to check with you here. Which is not something I usually do! I appreciate reviewing a blog post that will certainly make individuals believe. Likewise, thanks for permitting me to comment!

  13. check it out

    I?d have to talk to you right here. Which is not something I generally do! I take pleasure in reading a blog post that will certainly make individuals believe. Likewise, thanks for permitting me to comment!

댓글 남기기

이메일은 공개되지 않습니다.