By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,795 Members | 1,216 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,795 IT Pros & Developers. It's quick & easy.

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

P: 17
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.
Oct 17 '06 #1
Share this Question
Share on Google+
3 Replies


P: 15
Hi, LimaCharlie,

You can use this UDF function:

Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION ToDate(@StrDate VARCHAR(20))
  2. RETURNS DATETIME
  3. AS
  4. BEGIN
  5.     DECLARE @Dt DATETIME, @FormattedDate VARCHAR(20)
  6.     SET @FormattedDate = LEFT(@StrDate, 3) + ' ' + RIGHT(@StrDate, 1) + ' 20' + SUBSTRING(@StrDate, 4, 2)
  7.     SET @Dt = CAST(@FormattedDate AS DATETIME)
  8.     RETURN @Dt
  9. END
  10.  
  11.  
Then you can sort correctly using this query:

Expand|Select|Wrap|Line Numbers
  1. SELECT PayPeriod
  2. FROM Table
  3. ORDER BY dbo.ToDate(PayPeriod)
Or you can use a single query (without UDF):

Expand|Select|Wrap|Line Numbers
  1. SELECT PayPeriod
  2. FROM Table
  3. ORDER BY CAST(LEFT(PayPeriod, 3) + ' ' + RIGHT(PayPeriod, 1) + ' 20' + SUBSTRING(PayPeriod, 4, 2) AS DATETIME)
But it looks messy.

I hope this helps
Oct 17 '06 #2

P: 15
and to answer your 2 other questions...

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

Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION ToMonth(@StrMonth CHAR(3))
  2. RETURNS TINYINT
  3. AS
  4. BEGIN
  5.     DECLARE @Month TINYINT, @FormattedDate VARCHAR(20)
  6.     SET @FormattedDate = @StrMonth + ' 1 2000'
  7.     SET @Month = MONTH(CAST(@FormattedDate AS DATETIME))
  8.     RETURN @Month
  9. END
To get number of days in a month for a given year (because of feb):

Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION NoDays(@Month TINYINT, @Year INT)
  2. RETURNS TINYINT
  3. AS
  4. BEGIN
  5.     DECLARE @NoDays TINYINT, @FormattedDate VARCHAR(20), @DT DATETIME
  6.     SET @FormattedDate = CAST(@Month AS VARCHAR) + '/1/' + CAST(@Year AS VARCHAR)
  7.     SET @DT = CAST(@FormattedDate AS DATETIME)
  8.     SET @DT = DATEADD(m, 1, @DT)
  9.     SET @DT = DATEADD(d, -1, @DT)
  10.     SET @NoDays = DAY(@DT)
  11.     RETURN @NoDays
  12. END
Oct 17 '06 #3

P: 17
Hi galexyus,

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

Thanks.
God Bless.
Oct 18 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.