Convert Month Name to Month Number

Question

Possible Duplicate:
Convert Month Name to Month Number Function in SQL

I am attempting to convert a month name to month number in the area of the script below called datename(MM, patientinfo.PatientDOB). As an example, it is currently returning as January and I would like it to return as 01 or 1.

From what I've read I may need to do a CASE WHEN 'January' THEN 1, but I am not sure where to insert it or the exact syntax to use.

select
    Update_Log.RecordID as 'Dictation ID',
    cast(dictations.DOS as DATE) as 'Date of Service',
    groups.GroupName,
    rtrim(aspnet_Users.Firstname)+' '+rtrim(aspnet_Users.LastName) as Provider, 
    Update_Log.Updated, 
    Update_XRef.columnName as 'Update Type', 
    "New Value" =
        case 
            when update_xref.columnname = 'FacilityID' then (select facility.FacilityName+' [gID:'+update_log.NewValue+']' from facility where update_log.NewValue = facility.FacilityID)
            when update_xref.columnname = 'DOS' then (select left(update_log.NewValue, 11) as 'New Value')
            when update_xref.columnname = 'DictatorID' then (select rtrim(aspnet_users.Firstname)+' '+rtrim(aspnet_users.lastname) from aspnet_Users where aspnet_users.userid1 = update_log.UpdatedBy)
            when update_xref.columnname = 'PatientID' then (select rtrim(patientinfo.PatientFirst)+' '+rtrim(patientinfo.PatientLast)+' ['+datename(MM, patientinfo.PatientDOB)+'/'+datename(DD, patientinfo.PatientDOB)+'/'+datename(YY, patientinfo.PatientDOB)+ ', gID:'+rtrim(patientinfo.PatientID)+']' from patientinfo where patientinfo.PatientID = Update_Log.NewValue)
        end,
    "Old Value" =
        case 
            when update_xref.columnname = 'FacilityID' then (select facility.FacilityName+' [gID:'+update_log.OldValue+']' from facility where update_log.OldValue = facility.FacilityID)
            when update_xref.columnname = 'DOS' then (select left(update_log.OldValue, 11) as 'Old Value')
            when update_xref.columnname = 'DictatorID' then (select rtrim(aspnet_users.Firstname)+' '+rtrim(aspnet_users.lastname) from aspnet_Users where aspnet_users.userid1 = update_log.UpdatedBy)
            when update_xref.columnname = 'PatientID' then (select rtrim(patientinfo.PatientFirst)+' '+rtrim(patientinfo.PatientLast)+' ['+left(datename(MM, patientinfo.PatientDOB),3)+'/'+datename(DD, patientinfo.PatientDOB)+'/'+datename(YY, patientinfo.PatientDOB)+ ', gID:'+rtrim(patientinfo.PatientID)+']' from patientinfo where patientinfo.PatientID = Update_Log.OldValue)
        End
from 
    Update_Log, 
    Update_XRef, 
    aspnet_Users, 
    groups, 
    Dictations
where 
    Update_log.XRefID = Update_XRef.xRefID
    and Update_Log.UpdatedBy = aspnet_Users.userid1
    and aspnet_users.Groupid = Groups.GroupID
    and dictations.Dictationid = Update_Log.RecordID
    and Update_Log.Updated > dictations.DateofSignature
order by 
    groups.GroupName, 
    aspnet_users.LastName, 
    update_log.RecordID

score 3 · Accepted Answer · answered Oct 30 '12 at 14:58

3

Assuming patientinfo.PatientDOB is a full & typed date, to get the integer month part of the date;

datepart(MM, patientinfo.PatientDOB)

or

month(patientinfo.PatientDOB)

wrapped in cast(?? as varchar(2)) for a character string.

answered Oct 30 '12 at 14:58

Alex K.

171,639
30
264
288

Thank you for your response. When using the month or datepart, I receive the following messageL – Anthony Morack Oct 30 '12 at 17:44
Conversion failed when converting the nvarchar value 'JOHN SMITH [October/' to data type int. – Anthony Morack Oct 30 '12 at 17:45
`datepart` returns an integer, you cannot add it to a string without converting it to a string; `select 'text or a char field ' + cast(datepart(MM, patientinfo.PatientDOB) as varchar(2)) + ' XXX...'` – Alex K. Oct 30 '12 at 18:03
Thank you Alex! You rock! I see now that you mentioned the cast in the first post, but I did not have the knowledge to put two and two together :) – Anthony Morack Oct 30 '12 at 18:06
Your welcome. `right('0' + cast(datepart(MM, patientinfo.PatientDOB) as varchar(2)), 2)` to pad with `0`. – Alex K. Oct 30 '12 at 18:10

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

I answered this previously here:

Convert month name to month number in SQL Server

Let me know if this works for you!

edited May 23 '17 at 10:25

Community

1
1

answered Oct 30 '12 at 15:39

Ric

12,855
3
30
36

Convert Month Name to Month Number

2 Answers2