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

1. Registered User
Join Date
Oct 2005
Posts
119

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

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.

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Any DBA who stores dates as string should be shot.
Code:
```declare @CrappyDateString varchar(20)

set	@CrappyDateString = 'OCT06-1'

select	datepart(m,
convert(datetime, left(@CrappyDateString, 3) + ' '
+ substring(@CrappyDateString, 7, 2) + ' '
+ substring(@CrappyDateString, 4, 2)))```

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
1) see 2)

2) use something like this --
Code:
```select PayPeriod
, ( charindex(left(PayPeriod,3)
,'JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
+ 2 ) / 3 as PayPeriod_numeric
,  ...```

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Extra credit for thinking outside the box...

5. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally Posted by blindman
Any DBA who stores dates as string should be shot.
You shouldn't really bottle up your feelings....

In any case, I would add a new column and convert all the data into valid dates and then try to attack any reporting problems...or maybe even create a derived column..

Code:
```USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(25))
GO

INSERT INTO myTable99(Col1)
SELECT 'SEP06-1'UNION ALL
SELECT 'SEP06-2'UNION ALL
SELECT 'OCT06-1'UNION ALL
SELECT 'OCT06-2'
GO

SELECT * FROM myTable99

SELECT    CONVERT(datetime,
LEFT(Col1,3) + ' '
+ CASE WHEN RIGHT(Col1,1) = 2 THEN '15' ELSE '01' END + ' '
+ '20'+SUBSTRING(Col1,4,2) + ' '
)
FROM myTable99
GO

ALTER TABLE myTable99 ADD myDateCol AS
CONVERT(datetime,
LEFT(Col1,3) + ' '
+ CASE WHEN RIGHT(Col1,1) = 2 THEN '15' ELSE '01' END + ' '
+ '20'+SUBSTRING(Col1,4,2) + ' '
)
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO```

6. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
An added bonus I think is that it will act like a contraint on your varchar "mask"...I'll have to test that

EDIT: Nope, you can insert a non-comforming data, and you get the error when you reference it

7. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
2) Do you want to extract 1 and 1 for JAN06-1 and JAN06-2
or do you want 12 an 1? See ans 2.1 and 2.2

Assuming the 15th is the last day of a month
Code:
```select * into #t1 from (select 'PayPeriod'=
'OCT06-1' union all select
'OCT06-2' union all select
'FEB06-1' union all select
'FEB06-2' union all select
'JAN06-1' union all select
'JAN06-2' union all select
'SEP06-1' union all select
'SEP06-2' union all select
'MAR06-1' union all select
'MAR06-2' union all select
'APR06-1'
) v1

select PayPeriod
,'ans2.1'=datepart(mm,'01'+left(PayPeriod,5))
,'ans2.2'=datepart(mm,
case when right(PayPeriod,1)=1
else convert(datetime,'01'+left(PayPeriod,5))
end )
,convert(datetime,'15'+left(PayPeriod,5)))
from #t1
-- 'Ans1'= order by case statement in ans2.2 or
order by convert(datetime,'01'+left(PayPeriod,5)),right(PayPeriod,1)

drop table #t1```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•