Problem with Expression - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Problem with Expression

Author

Topic

TeEssKa
Starting Member

5 Posts

Posted - 2012-01-19 : 05:35:16

Hi there,

I´ve been desperately trying to compare two Date-Values in two columns in a third one.

Now I tried to fix this with the following expression:

=Iif((Fix(DateDiff("s",Fields!Beginn.Value,Fields!EINGABEDAT.Value)/3600))=true,(Fix(DateDiff("s",Fields!Beginn.Value,Fields!EINGABEDAT.Value)/3600))+":"+((Fix(DateDiff("s",Fields!Beginn.Value,Fields!EINGABEDAT.Value)/3600))-(CDec(DateDiff("s",Fields!Beginn.Value,Fields!EINGABEDAT.Value)/3600))),(CDec(DateDiff("s",Fields!Beginn.Value,Fields!EINGABEDAT.Value)/3600)))

This returns an error, saying that the Input-String has the wrong format.

Any Ideas or other solutions ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-19 : 07:45:11

I didn't quite follow the logic you are trying to implement, but if you are trying to pick the later of two timestamps if they are within an hour, there may be easier ways to do it using built in functions of SSRS. Can you describe what you are trying to do in words with some sample data?

TeEssKa
Starting Member

5 Posts

Posted - 2012-01-19 : 08:44:02

Thank your for the reply so far.

There are two columns, "date of entry" and "begin". Both carry data in the format dd-mm-yyyy hh:mm:ss.
I want the time difference as (dd-)hh-mm between them to be shown in another column.
The Time-Difference between the columns reaches from a few minutes up to a week.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-19 : 12:02:56

Can you see if something like this would work for you? Here, I am pushing everything back to the beginning of time (Jan 1, 1900).

DAY(DATEADD("n",DATEDIFF("n",Fields!Beginn.Value,Fields!EINGABEDAT.Value),"1/1/1900"))
& " - " & HOUR(DATEADD("n",DATEDIFF("n",Fields!Beginn.Value,Fields!EINGABEDAT.Value),"1/1/1900"))
& " - " & MINUTE(DATEADD("n",DATEDIFF("n",Fields!Beginn.Value,Fields!EINGABEDAT.Value),"1/1/1900"))

TeEssKa
Starting Member

5 Posts

Posted - 2012-01-23 : 03:01:37

Thank you very much. Now it almost does what it should !
There is just a tiny thing: The expression returns results, that sometimes differ by about a minute to what would be the true solution.

e.g.: 01.12.2011 17:10 02.12.2011 08:37 ---> returns: 1-15-26, should be 1-15-27 Seems to be a rounding-problem ?!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-23 : 07:08:45

If the example you posted are rounded - i.e., what you posted as 01.12.2011 17:10 02.12.2011 08:37 is really stored in the database as 01.12.2011 17:10 02.12.2011 08:36:55, with the 36 minutes and 55 seconds rounded to the next minute, this behavior can happen. The DATEDIFF functions are really counting the number of boundary crossings - in this case counting the number minute boundaries that are crossed.

Other than that, nothing comes to mind.

TeEssKa
Starting Member

5 Posts

Posted - 2012-01-24 : 04:57:38

Oh okay, thats negligible. Thank you for the effort so far !

Today, another issue came up. Now I need to include the working hours (0800 am - 0445 pm).
If begin later then 04:45 or earlier then 08:00, I want the time difference to be shown from 08:00 to the "date of entry".

=IIf((DatePart("h",Fields!Beginn.Value)>=16 AND (DatePart("m",Fields!Beginn.Value)>=45) OR
(DatePart("h",Fields!Beginn.Value)<=8)),(DateDiff("h",Fields!EINGABEDAT.Value,"8")),
(DAY(DATEADD("n",DATEDIFF("n",Fields!Beginn.Value,Fields!EINGABEDAT.Value),"1/1/1900"))& " - " &
HOUR(DATEADD("n",DATEDIFF("n",Fields!Beginn.Value,Fields!EINGABEDAT.Value),"1/1/1900"))& " - " &
MINUTE(DATEADD("n",DATEDIFF("n",Fields!Beginn.Value,Fields!EINGABEDAT.Value),"1/1/1900"))))

That's what I tried, but it won't work that way...

TeEssKa
Starting Member

5 Posts

Posted - 2012-01-24 : 07:32:33

I finally got it, now I have to implement public holidays...

=IIf((DatePart("h",Fields!Beginn.Value)>=16.75 OR
(DatePart("h",Fields!Beginn.Value)<8)),(DatePart("h",Fields!EINGABEDAT.Value))-8 &" : "&
(DatePart("n",Fields!EINGABEDAT.Value)),
(HOUR(DATEADD("n",DATEDIFF("n",Fields!Beginn.Value,Fields!EINGABEDAT.Value),"1/1/1900"))& " : "
& MINUTE(DATEADD("n",DATEDIFF("n",Fields!Beginn.Value,Fields!EINGABEDAT.Value),"1/1/1900"))))

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-24 : 08:06:00

Glad you got it working!

It might be best to implement holidays using a calendar table that lists the holidays, especially so if you need to internationalize your application. This article by Joe Celko would be a good read: http://www.simple-talk.com/sql/t-sql-programming/temporal-data-techniques-in-sql-/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources