Kamis, 30 Agustus 2012

convert month name to number sql

Use Combine DATEPART() and CAST() SQL Server Function.

For example:
SELECT DATEPART(mm,CAST('February'+ ' 1900' AS DATETIME))NumOfThisMonth

#Results:
NumOfThisMonth
2

select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth from (
    select (1)OrdNum,'January'MonthNames union
    select (2),'February' union
    select (3),'March' union
    select (4),'April' union
    select (5),'May' union
    select (6),'June' union
    select (7),'July' union
    select (8),'August' union
    select (9),'September' union
    select (10),'October' union
    select (11),'November' union
    select (12),'December'
)v0

Compare with this another way:
SELECT CHARINDEX('OCT','XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber

#Results:
monthNumber
10

select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth,
CHARINDEX(MonthNames,'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber
from (
    select (1)OrdNum,'JAN'MonthNames union
    select (2),'FEB' union
    select (3),'MAR' union
    select (4),'APR' union
    select (5),'MAY' union
    select (6),'JUN' union
    select (7),'JUL' union
    select (8),'AUG' union
    select (9),'SEP' union
    select (10),'OCT' union
    select (11),'NOV' union
    select (12),'DEC'
)v1

OrdNum    NumOfMonth    monthNumber
1         1             1
2         2             2
3         3             3
4         4             4
5         5             5
6         6             6
7         7             7
8         8             8
9         9             9
10        10            10
11        11            11
12        12            12

Tidak ada komentar:

Posting Komentar