Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

Format Date Time in query

Hi There,

I want to output a Date/Time field in 'April-05' format. Here is my existing query:

SELECT     Month
FROM         ReportPackData
GROUP BY Month
HAVING      (Month IS NOT NULL)
ORDER BY Month

This query is the datasource for a combo box where the user will select a particular month/year. The combo boxes are part of Reporting Services, and therefore cannot be formatted.

Thanks for your help.

LoveToSpod
0
LoveToSpod
Asked:
LoveToSpod
  • 6
  • 6
  • 3
  • +3
5 Solutions
 
ShogunWadeCommented:
I assume the column names Month is actually a datetime column?  

Incidentally its v unwise to call a column month   since month is a reserved word.
0
 
ispalenyCommented:
select DATENAME(month, getdate()) +'-'+RIGHT('0'+DATENAME(day, getdate()),2)
0
 
ispalenyCommented:
Of course you must replace "getdate()" function by a name of your datetime column.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
TimCotteeCommented:
Hi LoveToSpod,

Try:

Select
    DateName(month,[Month]) + '-'+Cast(DatePart(year,[Month]) as varchar) As [Month]
From
    ReportPackData
Group By [Month]
Having [Month] Is Not Null
Order By [Month]

Should give you April-2005, if you want to trim it to two digit years use:

    DateName(month,[Month]) + '-'+Right(Cast(DatePart(year,[Month]) as varchar),2) As [Month]


Tim Cottee
0
 
Brian CroweDatabase AdministratorCommented:
Why does everyone insist on formatting their data on the server side?
0
 
ShogunWadeCommented:
Thre are a number of reasons:  

1) when dumping data to file.
2) Gouping -- AS IN THIS CASE
3) testing queries. and checking by eye.
4) ... etc....
0
 
LoveToSpodAuthor Commented:
Hi All,

I have tried the various suggestions above.

ispaleny:
Yours returns an error due to an 'unexpected' datatype, and was suffixing the month with year '01'.

Tim:
Your query doesn't seem to handle the data as DateTime format

BriCrowe - As per the question, this data is the datasource for MS SQL Server Reporting Services combo box, which is a server-side application.

Your help appreciated

LoveToSpod
0
 
LoveToSpodAuthor Commented:
MODIFICATION:

ispaleny:
Yours returns an error due to an 'unexpected' datatype, and was suffixing ALL months with year '01'.

Tim:
Your query doesn't seem to handle the data as DateTime format.

The query MUST output the data in a date/time recognised format.

Cheers,

LoveToSpod
0
 
ShogunWadeCommented:
"The query MUST output the data in a date/time recognised format"

Hmm interesting   April-05  etc will not be recognised by any date parser as it is absent of the day number element.
0
 
LoveToSpodAuthor Commented:
Shogun

Entirely that!! The end user will only want to see the month and year he is selecting. This is why the day number is not required. The annoying thing is in asp it's probably really easy, however we're talking about sending the grouped list of dates to Reporting Services embedded object that has no formatting controls!! :( :(

What d'you reck??

Cheers,

LoveToSpod
0
 
ShogunWadeCommented:
can i just check ... is you [month] column a datetime column ?
0
 
ShogunWadeCommented:
I see exactly what you are trying to do and why.   Ive been there.    you have a report that filters by date but to want the user to pick a month and it will report on that month.    I never got to the botton of how to gracefully do this other than populate the dropdown with

Apr 2005
May 2005     etc


then have the stored proc for the report receive a varchar parameter,  this first line of which is  SET @MyDate=CONVERT(datetime,'01 ' + @MyParameter)
0
 
LoveToSpodAuthor Commented:
Shogun

The [Month] column is date/time

I have two month ComboBoxes that act as date-pickers. So ultimately the query runs like this.

SELECT     ReportPackData.*
FROM         ReportPackData
WHERE     (Month >= @MonthFrom) AND (Month <= @MonthTo)

This works well. The problem is the formatting in the Reporting Services combo box looks like this:

12/01/2004 00:00:00 - if selected would be value: 1st Dec 04.

I'm getting the feeling this is something to do with reporting services, and even if I can be provided the correct code, would not give the desired formatting result in the combo box, as it seems there are no formatting controls for the combo box. The dataType options for the ComboBox are String, Float, Boolean, Integer or DATE/TIME. The date/time option has no 'sub options' for the way the number is presented/formatted. This would seem to be the problem!! HUH!!

BTW Seems BriCrowe has gone quiet!

Cheers,

LoveToSpod
0
 
Brian CroweDatabase AdministratorCommented:
little distracted LoL

Besides it looks like you've got plent of help.  Nothing personal it's just that there's about 3-6 requests a day to format a datetime value in one way or another.  Most of the time it can be done on the server side.
0
 
Brian CroweDatabase AdministratorCommented:
I meant the client side
0
 
ispalenyCommented:
Sorry, It was month-day format. The right syntax is

select DATENAME(month, getdate()) +'-'+RIGHT('0'+DATENAME(year, getdate()),2)

It returns varchar and Reporting Services are not able to map it to a real datetime value. If you plan to use it as a report parameter, create a table mapping these value to datetimes and use it in your source view.

0
 
ShogunWadeCommented:
Yep, love I pretty much concluded the same thing.   Im not sure what to suggest really.
0
 
PSSUserCommented:
LoveToSpod, when I need to group by month and year within a report and yet still have it sorted in a sensible order and useable in range selection criteria (such as (Month >= @MonthFrom) AND (Month <= @MonthTo)), I actually make use of 2 fields.

The first is a text field in the format 'YYYY-MM', which can be used for grouping/sorting and selection.
The second, which is the one I actually display on the report is in the format 'MMM-YY'.

So the fields you want will be:

Cast(DatePart(year,[Month]) + '-' + DateName(month,[Month]) as varchar) As [SortMonth],
DateName(month,[Month]) + '-'+Cast(DatePart(year,[Month]) as varchar) As [DisplayMonth]

Not sure if this helps with what you are trying to achieve.
0
 
LoveToSpodAuthor Commented:
OK, time to close the question.

It seems Reposrting Services can be a little funky in the way it shows data within its charts. Date/time can be formatted, and to be honest I was going wrong with my formatting code. "mmm-yy" and "MMM-YY" are two completely different things. Lowercase 'm' is minutes and Uppercase 'M' is Month. Argghh!!! This ultimately solved the problem.

Thanks for your help, I have shared out the points accordingly.

LoveToSpod
0
 
LoveToSpodAuthor Commented:
ispaleny

I used your method of text and date/time alongside each other to work the reports. This is a tidy way of doing it, thank you.

LTS
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now