Convert to Month Name and Year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert to Month Name and Year

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-07 : 15:00:11

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 --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter On Facility
(
(NULL IS NOT NULL AND pv.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Company
(
(NULL IS NOT NULL AND pv.CompanyId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on date range
pv.Visit >= ISNULL(NULL,'1/1/1900') AND pv.Visit < dateadd(day,1,ISNULL(NULL,'1/1/3000'))

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-01-07 : 15:16:10

If you are doing this to format the data, that is a presentation issue and is better handled by Crystal than by SQL Server.

If you are trying to group the data by month, then you just need to truncate the datetime value to a whole month. This would work:

select dateadd(month, datediff(month, 0, [DateTimeValue]), 0) as ReportMonth

e4 d5 xd5 Nf6

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-01-07 : 15:59:06

Never, ever, ever format dates like this in T-SQL when you can simply do it in Crystal Reports. This is exactly what crystal reports is designed to do, and what t-sql is not!

It is so easy to format a datetime in crystal. Just add the standard datetime field to the report, right-click on it, and choose the format. You can use the pre-existing formats, or create your own with simple mm-dd-yyyy style specifications.

Isn't that much easier than trying to write long date calculations for this in T-SQL?

- Jeff
http://weblogs.sqlteam.com/JeffS

pleitch
Starting Member

8 Posts

Posted - 2008-01-09 : 22:56:00

Agreed. You wouldn't make an client side program try to maintain database referential integrity, any more than you would program a database server to format output.

pleitch
Starting Member

8 Posts

Posted - 2008-01-09 : 22:58:25

quote:
Originally posted by pleitch

Agreed. You wouldn't make an client side program try to maintain database referential integrity, any more than you would program a database server to format output.

That said - this is what you are after:
select datename(month,getdate()) + ' ' + datename(year, getdate())

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources