MSSQL2000日期转换为英文日期格式
1、如日期:2012-11-5
月份用缩写 月份.日,年
要求：NOV.5,20122、2344.56
可否用SQL翻译成英文的读法？？缩写
一月：January Jan.
二月：February Feb.
三月：March Mar.
四月：April Apr.
五月：May -
六月：June -
七月：July -
八月：August Aug.
九月：September Sept.
十月：October Oct.
十一月：November Nov.
十二月：December Dec
1、如日期:2012-11-5
月份用缩写 月份.日,年
要求：NOV.5,20122、2344.56
可否用SQL翻译成英文的读法？？缩写
一月：January Jan.
二月：February Feb.
三月：March Mar.
四月：April Apr.
五月：May -
六月：June -
七月：July -
八月：August Aug.
九月：September Sept.
十月：October Oct.
十一月：November Nov.
十二月：December Dec
year(日期列),day(日期列) ,缩写和年，日组串即可。
set @date='2012-11-5'
select convert(varchar(11),@date)
DECLARE @d DATETIME
SET @d = GETDATE()
SELECT CONVERT(VARCHAR(11) , @d)
SET language '简体中文'
/*
(无列名)
Oct 24 2012
*/
谢谢
2、2344.56元 (金额)
可否用SQL翻译成英文的读法？？
谢谢
2、2344.56元 (金额)
可否用SQL翻译成英文的读法？？
请问 2、2344.56元 (金额)
可否用SQL翻译成英文的读法？？
哦,原来SQL不是全能的.
请问月份如果不要缩写显示应该如何修改呢 November Nov 要全写
不过可以用过其它方法得到完整的DECLARE @Today DATETIME
SET @Today = GETDATE()
SET LANGUAGE us_english
SELECT DATENAME(month, @Today) AS 'Month Name'
GO
http://www.club-oracle.com/forums/sql-to-convert-number-to-words-t657/有兴趣你可以把下面的VBS代码写成一个函数。public Function NumToText(dblVal As Double, CurrencyCode As String) As String
Static Ones(0 To 9) As String
Static Teens(0 To 9) As String
Static Tens(0 To 9) As String
Static Thousands(0 To 4) As String
Static bInit As Boolean
Dim i As Integer, bAllZeros As Boolean, bShowThousands As Boolean
Dim strVal As String, strBuff As String, strTemp As String
Dim nCol As Integer, nChar As Integer
Dim CurrencyUnit As String, CurrencyCents As String
'Only handles positive values
if dblVal < 0 then
exit function
end if If bInit = False Then
'Initialize array
bInit = True
Ones(0) = "zero "
Ones(1) = "one "
Ones(2) = "two "
Ones(3) = "three "
Ones(4) = "four "
Ones(5) = "five "
Ones(6) = "six "
Ones(7) = "seven "
Ones(8) = "eight "
Ones(9) = "nine "
Teens(0) = "ten "
Teens(1) = "eleven "
Teens(2) = "twelve "
Teens(3) = "thirteen "
Teens(4) = "fourteen "
Teens(5) = "fifteen "
Teens(6) = "sixteen "
Teens(7) = "seventeen "
Teens(8) = "eighteen "
Teens(9) = "nineteen "
Tens(0) = " "
Tens(1) = "ten "
Tens(2) = "twenty "
Tens(3) = "thirty "
Tens(4) = "forty "
Tens(5) = "fifty "
Tens(6) = "sixty "
Tens(7) = "seventy "
Tens(8) = "eighty "
Tens(9) = "ninety "
Thousands(0) = ""
Thousands(1) = "thousand " 'US numbering
Thousands(2) = "million"
Thousands(3) = "billion"
Thousands(4) = "trillion"
End If
'Trap errors
On Error GoTo NumToTextError
'Get fractional part
Select Case CurrencyCode
case "MNT"
CurrencyUnit="tugrug"
CurrencyCents="mungu"
case "USD"
CurrencyUnit="dollars"
CurrencyCents="cents"
case "AUD"
CurrencyUnit="dollars"
CurrencyCents="cents"
case "EUR"
CurrencyUnit="euros"
CurrencyCents="cents"
end select
If dblVal - Int(dblVal) > 0 Then
strBuff = Cstr(CurrencyUnit) & " and " & lcase(trim(replace(NumToText(Int(Format((dblVal - Int(dblVal)) * 100, "00")),""),"only",""))) & " "& Cstr(CurrencyCents) & " only"
Else
strBuff = Cstr(CurrencyUnit) & " only "
End If ' strBuff = "and " & Format((dblVal - Int(dblVal)) * 100, "00 ") & "/100 "
'Convert rest to string and process each digit
strVal = CStr(Int(dblVal))
'Non-zero digit not yet encountered
bAllZeros = True
'Iterate through string
For i = Len(strVal) To 1 Step -1
'Get value of this digit
nChar = Val(Mid$(strVal, i, 1))
'Get column position
nCol = (Len(strVal) - i) + 1
'Action depends on 1 's, 10 's or 100 's column
Select Case (nCol Mod 3)
Case 1 '1 's position
bShowThousands = True
If i = 1 Then
'First digit in number (last in loop)
strTemp = Ones(nChar) ' & " "
ElseIf Mid$(strVal, i - 1, 1) = "1" Then
'This digit is part of "teen " number
strTemp = Teens(nChar) '& " "
i = i - 1 'Skip tens position
ElseIf nChar > 0 Then
'Any non-zero digit
strTemp = Ones(nChar) ' & " "
Else
'This digit is zero. If digit in tens and hundreds column
'are also zero, don 't show "thousands "
bShowThousands = False
'Test for non-zero digit in this grouping
If Mid$(strVal, i - 1, 1) <> "0" Then
bShowThousands = True
ElseIf i > 2 Then
If Mid$(strVal, i - 2, 1) <> "0" Then
bShowThousands = True
End If
End If
strTemp = ""
End If
'Show "thousands " if non-zero in grouping
If bShowThousands Then
If nCol > 1 Then
strTemp = strTemp & Thousands(nCol \ 3)
If bAllZeros Then
strTemp = strTemp ' & " "
Else
strTemp = strTemp & ", "
End If
End If
'Indicate non-zero digit encountered
bAllZeros = False
End If
strBuff = strTemp & strBuff
Case 2 '10 's position
If nChar > 0 Then
If Mid$(strVal, i + 1, 1) <> "0 " Then
strBuff = Tens(nChar) & "" & strBuff
Else
strBuff = Tens(nChar) & "" & strBuff
End If
End If
Case 0 '100 's position
If nChar > 0 Then
If Val(Mid$(strVal, i+1, 1))="0" and Val(Mid$(strVal, i+2, 1))="0" Then
strBuff = Ones(nChar) & "hundred " & strBuff
Else
strBuff = Ones(nChar) & "hundred and " & strBuff
End if
End If
End Select
Next i
'Convert first letter to upper case
strBuff = UCase$(Left$(strBuff, 1)) & Mid$(strBuff, 2)
EndNumToText:
'Return result
NumToText = strBuff
Exit Function
NumToTextError:
strBuff = "#Error# "
Resume EndNumToText
End Function