MSSQL 에서 천단위 컴마를 찍고 싶을 때

 

SELECT CONVERT(VARCHAR(50), CAST(20000 AS MONEY), 1)

 

결과는  20,000.00

 

MONEY 타입은 소숫점 둘째자리까지 표현됨.

소숫점을 없애고 싶으면 REPLACE 하면됨

 

SELECT  REPLACE(CONVERT(VARCHAR(50), CAST(20000 AS MONEY), 1) , '.00', '')

 

결과는 20,000

 

 

 

MSSQL의 CAST AND CONVERT 도움말 중

money and smallmoney Styles

When expression is money or smallmoney, style can be one of the values shown in the following table. Other values are processed as 0.

Value

Output

0 (default)

No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

1

Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

2

No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

126

Equivalent to style 2 when converting to char(n) or varchar(n)

 

 

 

 

참고  : http://msdn.microsoft.com/en-us/library/ms187928.aspx


날짜 형식..  "yyyy-mm-dd hh:mi:ss"

MSSQL 에서  

Convert(char(19), GETDATE(), 120)
MYSQL 에서는
CAST( DATE_FORMAT( NOW(),'%Y-%m-%d %H:%i:%s' ) AS CHAR(19) )


날짜 형식..  "yyyymmdd"

MSSQL 에서
Convert(char(8), GETDATE(), 112)


MYSQL 에서는
CAST( DATE_FORMAT( NOW(),'%Y%m%d' ) AS CHAR(8) )


 

+ Recent posts