Convert month into its number equivalent...(E x. JAN=1,FEB=2...)

[LimaCharlie]

Good Day to Everyone,

Need help!

I have this SQL table w/ fieldname "PayPeriod" . Sample value of PayPeriod are SEP06-1, SEP06-2, OCT06-1, OCT06-2... Wherein the first 3 chars is the month, 06 is the year, 1 or 2 is the cutoff where 1 means the 2nd half of the previous month and 2 is the first half of the current month.

Now my questions are:
1.) How can I select these values sorted according to date. Since this is a character data type. Sorting is alphabetical. I need to sort it according to date where JAN05-1 is the first record and the last record is DEC06-2 (assuming that the oldest year is 05 and 06 is the latest).
2.) How can I convert the 3 char month into its number equivalent. Example, JAN is equal to 1, FEB is 2, MAR is 3....
3.) How can I count the no. of days in a month? Is there a function that would result to the no. of days in a given month?

Thank you so much in advance.

[galexyus]

Hi, LimaCharlie,

You can use this UDF function:

CREATE FUNCTION ToDate(@StrDate VARCHAR(20))
RETURNS DATETIME
AS
BEGIN
    DECLARE @Dt DATETIME, @FormattedDate VARCHAR(20)
    SET @FormattedDate = LEFT(@StrDate, 3) + ' ' + RIGHT(@StrDate, 1) + ' 20' + SUBSTRING(@StrDate, 4, 2)
    SET @Dt = CAST(@FormattedDate AS DATETIME)
    RETURN @Dt
END
 
 

Then you can sort correctly using this query:

SELECT PayPeriod
FROM Table
ORDER BY dbo.ToDate(PayPeriod)

Or you can use a single query (without UDF):

SELECT PayPeriod
FROM Table
ORDER BY CAST(LEFT(PayPeriod, 3) + ' ' + RIGHT(PayPeriod, 1) + ' 20' + SUBSTRING(PayPeriod, 4, 2) AS DATETIME)

But it looks messy.

I hope this helps

[galexyus]

and to answer your 2 other questions...

To get the month number, you can use this function:

CREATE FUNCTION ToMonth(@StrMonth CHAR(3))
RETURNS TINYINT
AS
BEGIN
    DECLARE @Month TINYINT, @FormattedDate VARCHAR(20)
    SET @FormattedDate = @StrMonth + ' 1 2000'
    SET @Month = MONTH(CAST(@FormattedDate AS DATETIME))
    RETURN @Month
END

To get number of days in a month for a given year (because of feb):

CREATE FUNCTION NoDays(@Month TINYINT, @Year INT)
RETURNS TINYINT
AS
BEGIN
    DECLARE @NoDays TINYINT, @FormattedDate VARCHAR(20), @DT DATETIME
    SET @FormattedDate = CAST(@Month AS VARCHAR) + '/1/' + CAST(@Year AS VARCHAR)
    SET @DT = CAST(@FormattedDate AS DATETIME)
    SET @DT = DATEADD(m, 1, @DT)
    SET @DT = DATEADD(d, -1, @DT)
    SET @NoDays = DAY(@DT)
    RETURN @NoDays
END

[LimaCharlie]

Hi galexyus,

Thank you so much!
I got it already.
You're such a big help.

Thanks.
God Bless.