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
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
select DATENAME(month, getdate()) +'-'+RIGHT('0'+DATENAME(da
Of course you must replace "getdate()" function by a name of your datetime column.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why does everyone insist on formatting their data on the server side?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
"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.
Hmm interesting April-05 etc will not be recognised by any date parser as it is absent of the day number element.
ASKER
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
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
can i just check ... is you [month] column a datetime column ?
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,'
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,'
ASKER
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
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
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yep, love I pretty much concluded the same thing. Im not sure what to suggest really.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
Incidentally its v unwise to call a column month since month is a reserved word.