Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Display mmddyyyy-mmddyyyy database date range field to display in alphanumerical format

Status
Not open for further replies.

hmax

Programmer
Jan 22, 2012
60
US
Crystal Reports 10
MS SQL Server 2008 R2

How do I convert a database date range field (format mm/dd/yyyy-mm/dd/yyyy) to display as, e.g.,

October 24th through November 4th, 2016


Thanks in advance!
 
Getting the month (monthname function) and year (year function). The day is tricky since it can be a suffix of st, nd, rd or th. But a if or switch statement might work for this.

i.e., monthname(datefield)+ ' ' + totext(day(datefield))+if day(datefield) = 1 then 'st ' else if day(datefield) =2 then 'nd ' else if day(datefield) = 3 the 'rd ' else 'th ' +year(datefield) + ' through ' + (repeat the first part of the formula for the second datefield.
 
Thanks, kray4660.

It's tricky since it's a range. I'm creating formulae, breaking down each piece w/left and mid functions (and of course days could be one or two chars so must allow for that). Just looking for a more elegant approach than mine :)

I'll give your ideas a try!

hmax
 
Unfortunately for what you are doing, I do not know of a more elegant solution. Even if you are just using one field, you can use the minimum and maximum functions to get the start and end dates.
 
For the period "&minimum({?Date Range})&" thru "&maximum({?Date Range})
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top