Convert to Month Name and Year 1

[Tech2377]

I would like to add in another field based off the pv.Visit

Example: Currently, the query is reporting a date as such:
'2007-10-25 10:15:00.000'

I would like a Field that converts this into one field 'October 2007'. This would allow me to do easier work in Crystal. Any help is appreciated.

SELECT pp.PatientId, 
dbo.FormatName(pp.Prefix, pp.[First], pp.Middle, pp.[Last], pp.Suffix) AS PatName, 
pv.Visit, 
df.ListName,
pv.TicketNumber

FROM PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId

WHERE ......

 

[gmmastros]

Select DateName(Month, GetDate()) + ' ' + Convert(VarChar(4), Year(GetDate()))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ca8msm]

DATENAME


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Tech2377]

I'm getting January 2008 for all ... ?

 

[gmmastros]

Because you need to replace [!]GetDate()[/!] with your actual column name.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Tech2377]

George ...

Boy how did I miss that last one ... so obvious after I read your response. I tried a different approach ... however it does what I want it to (to an extent). Months of November and December get put out of order due to the month # starting with a '1'. Any thoughts on a workaround?

SELECT  pp.PatientId, 
	dbo.FormatName(pp.Prefix, pp.[First], pp.Middle, pp.[Last], pp.Suffix) AS PatName, 
	pv.Visit, 
	MonthNumber=convert(varchar(2),datepart(month,pv.visit)), 
	(Select DateName(Month, pv.visit)) AS VisitMonth,  
	(SELECT Convert(VarChar(4), Year(pv.visit))) AS VisitYear,
	df.ListName,
	pv.TicketNumber

FROM    PatientVisit pv
	INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
	INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId

WHERE ....

ORDER BY [VisitYear], [MonthNumber] ASC

 

[Tech2377]

Month of October too ... oops

So those 3 months get out of order due to starting with a '1'

 

[gmmastros]

Order By PV.Visit



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jdaman]

SELECT  pp.PatientId,
    dbo.FormatName(pp.Prefix, pp.[First], pp.Middle, pp.[Last], pp.Suffix) AS PatName,
    pv.Visit,
    MonthNumber=convert(varchar(2),datepart(month,pv.visit)),
    (Select DateName(Month, pv.visit)) AS VisitMonth,  
    (SELECT Convert(VarChar(4), Year(pv.visit))) AS VisitYear,
    df.ListName,
    pv.TicketNumber

FROM    PatientVisit pv
    INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
    INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId

WHERE ....

ORDER BY [VisitYear], [COLOR=red]pv.Visit[/color] ASC

 

[gmmastros]

Let me comment on your code just a bit. Please don't take this personally. The only reason I mention this stuff is to help you out.

1. There are 2 ways to alias a column.
a) MonthNumber = Convert(....
b) Convert(....) As MonthNumber

Both methods are acceptable, but I suggest you pick one and stick with it. In your query, you are using both methods.

2. You should, as much as possible, pass some 'work' on to the client application. For example, you are converting the MonthNumber to a varchar(2) before passing the data back. Why? Since DatePart returns an integer, why don't you just return that from the query? I'm sure Crystal can convert this to a string just as easily as SQL Server, but by having Crystal Reports doing more work, and SQL Server doing less work, your app will be more scalable. Will this change have much affect? No. But if you take this approach to all of your queries, eventually it will make a difference.

3. Your syntax, while it does work, is 'more' than it needs to be.

This: (Select DateName(Month, pv.visit)) AS VisitMonth,

Should just be:
DateName(Month, pv.visit) AS VisitMonth,

Again. It won't make much difference, but it all adds up.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom