Adding the time to a Date(DateTime)

Question

Adding the time to a Date(DateTime)

hunter_spawn

Grasshopper

Points: 24
More actions
March 7, 2008 at 2:23 pm

#187567

Hello, This thing is driving me crazy.
First of all, let me explain, this is a report which appears to have been made for a Version of a program called 395. I have no real way to know, but the tables used in the report are only into this version.
So the Diff between version is the table, their content, etc..
Due to some professionnal circumstances, i am unable to test the report on V395, I heard people say it used to work in the past (they dont know how which version it was running on).
I am trying to make it work on a previous version (381) and i fell on some "details" which gives me high doubt it ever worked like that...
Here is the problem i am here for.
=FormatDateTime(Parameters!DateDebut.Value & " " & Parameters!DateDebutHeure.Value & ":00:00")
(the weird name of parameters is called french ;))
So this is a parameter for a dataset. The idea in it is to generate a datetime which contains a date and a time. DateDebut is the date, and DateDebutHeure is simply a string which goes from 00 to 23.
When i run the report, input the date and generate the thing. I will get an error when the day is over 12. I am using the report parameter DateTime which uses the calendar to get the date.
It looks as if FormatDateTime was using wrong regional parameters. I'd like to have your advices, Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

It sounds to me like the DateDebut column is a VARCHAR column and could be in the dd/mm/yyyy format instead of the format you're expecting of mm/dd/yyyy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hunter_spawn Grasshopper Points: 24 More actions · Answer 2

hunter_spawn

Grasshopper

Points: 24

March 9, 2008 at 4:48 pm

#787164

DateDebut is a Parameter of type DateTime.

David.Poole SSC Guru Points: 76001 More actions · Answer 3

Date/Time formatting is always a problem. Unless you know the starting format and the locale then you are going to run into problems.

Even if you start with a date then if the server is set to US English then you get dates in the format mm/dd/yyyy.

I've given up on fancy formats and stuck with ISO8601 which is yyyy-mm-ddThh:mm:ss.000. That way, no matter what the locale, if I supply a date as 2008-02-10T00:00:00.000 I know it is Feb 10th 2008.

Some applications bounce the formats around between dates, varchars etc. It works right up until the time someone changes the locale or they query it from a server with a different locale.

LinkedIn Profile

hunter_spawn Grasshopper Points: 24 More actions · Answer 4

how can i configure my reports / the server to always use this format? Will it affect the command FormatDateTime?

hunter_spawn Grasshopper Points: 24 More actions · Answer 5

I found the mistake. I used CDate instead of FormatDateTime and it now works like a charm.