24-hour time format

I need to display a datetime in the following format:
31-10-2006 23:15:05 (day, month year with 24-hour time format).
I have tried this:
=Format(Fields!Date_Logged.Value,"dd-MM-yyyy hh:mm:ss")
but it shows the hour as 8:00 for AM and 8:00 for PM. I do NOT want to use
AM and PM (I know that would be 'tt' at the end), because I live in Denmark,
where 24-hours is used. How do I get the 24-hour hours to show?
Thanks in advance for any tips.You are very close. Just put the following in the format code exactly as I
have here:
MM/dd/yy HH:mm:ss
Also, although you can use the expression like you have done below, you
don't need to. Just put the expression code is, no = sign, no quotes.
Same thing for formatting numbers. I use this quite a bit
#,##0
The canned formats don't have this which I prefer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"yogi bear" <yogi bear@.discussions.microsoft.com> wrote in message
news:DC1A6B4A-00FB-4617-9098-28BB2830E957@.microsoft.com...
>I need to display a datetime in the following format:
> 31-10-2006 23:15:05 (day, month year with 24-hour time format).
> I have tried this:
> =Format(Fields!Date_Logged.Value,"dd-MM-yyyy hh:mm:ss")
> but it shows the hour as 8:00 for AM and 8:00 for PM. I do NOT want to use
> AM and PM (I know that would be 'tt' at the end), because I live in
> Denmark,
> where 24-hours is used. How do I get the 24-hour hours to show?
> Thanks in advance for any tips.
>

24 hr date format

couldn't find 24 hr(military) date format

thanks

Just use the Format() function, like this:

Code Snippet

Format( CDate([your field]), "HH:mm:ss")

or

Code Snippet

Format( CDate([your field]), "yyyyMMdd HH:mm:ss")

Hope that helps.

|||

Thanks. How can I subtract one datetime (2007-02-02 10:11:11) to another datetime(2007-02-02 11:10:10) and put negatives in bracket(red)

thanks in advance

|||

Here is an expression you can use to evaluate how many days difference there are between two dates:

Code Snippet

=DateDiff("d", CDate(Fields!FirstDate.Value), CDate(Fields!SecondDate.Value))

To subtract a specific number of days from a date use this (just change the -1 to whatever quantity you require):

Code Snippet

=DateAdd("d", -1, CDate(Fields!MyDate.Value))

To show a negative value in red, use an IIf statement in the Color property of the cell or textbox, like this:

Code Snippet

=IIf( [my date expression] < 0, "Red", "Black")

|||Thanks again..this thing will give me just the number of days , I am trying get the out put in the format -hh:mms and then if it is negative keep it in a bracket(hh:mms)|||

Hello,

Try this to get the difference in the format you require:

=Iif(DateDiff("s", Fields!FirstDate.Value, Fields!SecondDate.Value) < 0, "(", "")

+ cStr(abs(DateDiff("h", Fields!FirstDate.Value, Fields!SecondDate.Value)))

+ ":"

+ Format(abs(DateDiff("n", Fields!FirstDate.Value, Fields!SecondDate.Value) mod 60), "00")

+ ":"

+ Format(abs(DateDiff("s", Fields!FirstDate.Value, Fields!SecondDate.Value) mod 60), "00")

+ Iif(DateDiff("s", Fields!FirstDate.Value, Fields!SecondDate.Value) < 0, ")", "")

Then, if you want the negative values to be in red, enter this in the color property expression.

=Iif(DateDiff("s", Fields!FirstDate.Value, Fields!SecondDate.Value) < 0, "Red", "Black")

This will display in red if FirstDate is after the SecondDate.

Hope this helps.

Jarret

|||

A slightly more efficient way to do it is to abstract that code into a custom function, like this:

Code Snippet

Public Function TimeDiff(firstDate As String, secondDate As String) As String

Dim timeDifference As String
timeDifference = Abs(DateDiff("h", CDate(firstDate), CDate(secondDate))) & Format(CDate(firstDate) - CDate(secondDate), ":nn:ss")

If CDate(firstDate) > CDate(secondDate) Then
timeDifference = "-(" & timeDifference & ")"
End If

TimeDiff = timeDifference
End Function

then you can just call it from your cell with an expression like this:

Code Snippet

=Code.TimeDiff(Fields!FirstDate.Value, Fields!SecondDate.Value)

Or you can simplify the previously posted code into an expression directly in the cell:

Code Snippet

=IIf(
CDate(Fields!firstDate.Value) <= CDate(Fields!secondDate.Value),
Abs(DateDiff("h", CDate(Fields!firstDate.Value), CDate(Fields!secondDate.Value))) & Format(CDate(Fields!firstDate.Value) - CDate(Fields!secondDate.Value), ":nn:ss"),
"-(" & Abs(DateDiff("h", CDate(Fields!firstDate.Value), CDate(Fields!secondDate.Value))) & Format(CDate(Fields!firstDate.Value) - CDate(Fields!secondDate.Value), ":nn:ss") & ")"
)

You have already been shown two different expressions to colour the text, and you have been given two complete correct answers by two different people - i think you have enough to finish this one off and get your report looking good

2005: Publishing (not exporting) Report in Excel format

My end users want me to post 2 versions of my 2005 Reporting Services
reports. One that when they click on it automatically opens in Excel w/o
grouping...sort of a data dump. Then a second that is grouped, formatted and
pretty.
I know that I can have them export the report once run into Excel, but they
don't want that. (of course...;) So, does anyone know how to publish in
straight Excel in Reporting Services?
thanks in advance for any help!
MachelleYou didn't say if you are using Report Manager or not. The best way is to
use jump to URL and render in CSV ASCII format. The default for CSV is
unicode which Excel opens up with all the data in its own column.
Depending on your report you may or may not want to have a duplicate report
that you have cleaned up and call that one rather than the pretty one.
Regardless, if you have any data size at all URL is much faster.
If you are using Report Manager you could have a report that has no data,
just the parameters and then textboxes, one for pretty, one for Data Export
(I call it that in my link to differentiate that it is a data export, not
the pretty Excel export).
Note, the fields texboxes of the report have a name property that is set
during design to the name of the field. You can give it friendlier names if
you want.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Very nice and very fast.
Bruce Loehle-Conger
"Machelle" <Machelle@.discussions.microsoft.com> wrote in message
news:A5360BDE-440C-4AAF-8D07-46A9D3472567@.microsoft.com...
> My end users want me to post 2 versions of my 2005 Reporting Services
> reports. One that when they click on it automatically opens in Excel w/o
> grouping...sort of a data dump. Then a second that is grouped, formatted
> and
> pretty.
> I know that I can have them export the report once run into Excel, but
> they
> don't want that. (of course...;) So, does anyone know how to publish in
> straight Excel in Reporting Services?
> thanks in advance for any help!
> Machelle

2005 Management Studio Reformats my source!

Minor issues that are driving me crazy about this tool:
1. I type in a new view and format it the way it makes sense to me. I hit
the Save button, and SqlMS decides it wants to format it a completely
different way. How can I turn this feature off, or tell the program how I
want it to format my code? Older versions of EM did not reformat the code.
2. When I create a new view, the Diagram, Results and Criteria panes
automatically display. I have to click the buttons to hide them each time.
I don't need them or want them taking up editing space. How can I make the
program remember that I don't want it to display these "helpful" windows
each time?
Please advise
no help or resolution here?
"ZippyThePinhead" wrote:

> Minor issues that are driving me crazy about this tool:
> 1. I type in a new view and format it the way it makes sense to me. I hit
> the Save button, and SqlMS decides it wants to format it a completely
> different way. How can I turn this feature off, or tell the program how I
> want it to format my code? Older versions of EM did not reformat the code.
> 2. When I create a new view, the Diagram, Results and Criteria panes
> automatically display. I have to click the buttons to hide them each time.
> I don't need them or want them taking up editing space. How can I make the
> program remember that I don't want it to display these "helpful" windows
> each time?
> Please advise
>
|||Why not create the view as a CREATE VIEW statement inside of a Query window? I
don't seem to have any issues when I do that. Of course, you will need to
choose ALTER - Script to New Window/Clipboard in order to modify the code
instead of choosing Modify as Modify defaults to that view.
-Pete Schott
ZippyThePinhead <ZippyThePinhead@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> no help or resolution here?
> "ZippyThePinhead" wrote:
|||Thanks Peter -- yes that certainly does work, but it's a pain in the rear to
have to manage this myself..which is what I have been doing lately.
There simply needs to be some options available in the program to stop the
auto-formatting of our code. I've noticed that sometimes it even messes
things up by adding "as exp1" expressions on columns the parser thinks are
misnamed!
"Peter A. Schott" wrote:

> Why not create the view as a CREATE VIEW statement inside of a Query window? I
> don't seem to have any issues when I do that. Of course, you will need to
> choose ALTER - Script to New Window/Clipboard in order to modify the code
> instead of choosing Modify as Modify defaults to that view.
> -Pete Schott
> ZippyThePinhead <ZippyThePinhead@.discussions.microsoft.com> wrote:
>