DB accessing problems after moving the DB

Hi guys
I just moved my system from a workstation to a notebook. So far so good. But that's the beginning of my trouble. I backed up all databases I need for my developing work installed the SQL Server (Dev Edition) on the notebook an restored the databases. Following to that I enabled the Shared Memory, the TCP/IP and the Named Pipes for the Instance. When I now try to run an ASP.Net Website using one of my databases I'm getting this error message:

Cannot open database "DatabaseName" requested by the login. The login failed. Login failed for user 'DOMAIN\user.name'.

Additionalliy the log (you can find it under SSMS->Management->SQL Server Logs) reports following:
Error: 18456, Severity: 14, State: 16 what means that the incoming user does not have permissions to log into the target database.

I checked this out by logging the user into some other database (master) and then tryed using the USE DATABASE command to switch to the target database to get a better error message:

Msg 911, Level 16, State 1, Server ComputerName, Line 1
Could not locate entry in sysdatabases for database "DatabaseName". No entry found with that name. Make sure that the name is entered correctly.

Do you guys have any idea what I can do?

BTW: The notebook has two NICs. I don't know if has something to do with that.

SOunds kind of like your restore blew up somehow. Try stopping the SQL Server on the workstation, then copy the required MDF / LDF database FILES to the notebook and use the ATTACH method from SQL Management Console. You might also want to consider using SQL Server authentication rather than Windows.

|||

Many thanks! That solved my problem.

DateTime format during INSERT using MS JDBC driver

Hi,
Using the latest MS JDBC-driver:
The date format of the SQL Server causes me some trouble at the moment:
My INSERT statement uses DateTime values of the format "yyyy-mm-dd
hh:mm:ss.mmm" but they somehow get interpreted as"yyyy-dd-mm" causing a date
format out of range exception.
How can I "force" SQL Server to use my date format? Calling "SET DATEFORMAT
YMD" on each open connection before the statement is executed does not help
but this may be caused by additional connections being opened "behind the
scenes" (so I've been told). I'm not sure this is so even if my connection
pool keeps connections open.
The db-user opening the connection has the correct language setting and the
collation label of the databases is also correct.
So I'm wondering whether it's the regional setting of the Windows account
that runs the SQLServer-service (or SQLSERVERAGENT-Service?) that causes
this? But then - what region would use "yyyy-dd-mm" as it's date format?
Or is there a setting on the JDBC-driver that can modify this behaviour?
- Tim
Hi Tim,
You can either use the parameterized query and pass in a value of a
Java.Sql.Date type. You can thus convert the "yyyy-mm-dd" string to Date.
Below is the code snippet:
PreparedStatement st = connection1.prepareStatement("INSERT
Customers (ArchiveDate) VALUES (?)");
st.setDate(1, Date.valueOf("1999-01-30"));
st.executeUpdate();
Or if you choose to hard code the "yyyy-mm-dd" in the query string, you can
use CONVERT function to convert the string to a datetime sql data type
according to the proper style of date format. 120 is the ODBC canonical
style that converts yyyy-mm-dd hh:mi:ss(24h). Below is the code snippet:
PreparedStatement st = connection1.prepareStatement("INSERT
Customers (ArchiveDate) VALUES (CONVERT (datetime, '1999-01-30', 120) )");
st.executeUpdate();
For more info on CONVERT, please refer to the SQL Server Books Online.
Yilei

DateTime Format Codes

Hi everyone (again). I'm having trouble with some sate formats with the convert() function (I'm not finding the correct format code for "YYYY" format). Is it 100?
Sorry for the silly question
Thanks and best regards
Rafael Mauricio Nami
There is no direct style to get just the year format. You can do one of the following:

select cast(year(current_timestamp) as char(4))
, cast(datepart(year, current_timestamp) as char(4))
, convert(char(4), current_timestamp, 112)

Datetime comparison problem

Hi,
I am having trouble finding any records with a datetime of 2006-06-16
09:04:39:347. This time clearly falls between the beginning and end times.
I'm even converting to a char to see if that works. It seems to work if I
use .346 but sometimes it requires a difference of .003. Can anyone see what
I might be doing wrong? Thanks Ellie
declare @.dPrevDate datetime
declare @.dNextDate datetime
select @.dPrevDate = '2006-06-16 09:04:39.346'
select @.dNextDate = '2006-06-16 09:05:56.110'
Select *
where (convert(char, tblChanges.dateaction, 21) > convert(char,
@.dPrevDate, 21) and
convert(char, tblChanges.dateaction, 21) < convert(char, @.dNextDate,
21))Sorry I made typos, I should have said that it DIDN'T work for 346 but did
work for 345 (when 346 is clearly less than 347) and the code should be:
>
>
"Ellie" <nospam@.nospam.net> wrote in message
news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see
> what I might be doing wrong? Thanks Ellie
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char,
> @.dNextDate, 21))
>
>|||Due to a design flaw with Intel based chips, compute clock time is accurate
to the nearest three thousandth of a second.
This makes it difficult to coordinate precise times from a different time so
urce to the times created by a Intel based computer. For example, an automat
ed process control system have be recording times that are .003 seconds 'of
f' what the computer thinks.
You have to build in the .003 'fudge' factor.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ellie" <nospam@.nospam.net> wrote in message news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...[
color=darkred]
> Hi,
>
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see wh
at
> I might be doing wrong? Thanks Ellie
>
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
>
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char, @.dNextDate
,
> 21))
>
>
>
>[/color]|||I was thinking that there must have been a problem there but why can't I
convert it to a string and have it more accurate? That isn't working either.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
Due to a design flaw with Intel based chips, compute clock time is accurate
to the nearest three thousandth of a second.
This makes it difficult to coordinate precise times from a different time
source to the times created by a Intel based computer. For example, an
automated process control system have be recording times that are .003
seconds 'off' what the computer thinks.
You have to build in the .003 'fudge' factor.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ellie" <nospam@.nospam.net> wrote in message
news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see
> what
> I might be doing wrong? Thanks Ellie
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char,
> @.dNextDate,
> 21))
>
>|||I don't have any clue what "isn't working" means. However, I do have a
suggestion for storing accuracies under 3 ms.
When you enter the data to SQL Server, you can store the date +
minutes/seconds in datetime, then store *your* milliseconds in a separate
INT column. Or to save space you could use smalldatetime for date + minutes
and store seconds*1000+milliseconds in a separate INT column.
Now, when you query, you'll have something more complex to work with, and
that will be the harder part, but your data will be there. You can just
query for the times within your minute, and then where the milliseconds are
in the range you're after.
"Ellie" <nospam@.nospam.net> wrote in message
news:uTH03CvlGHA.884@.TK2MSFTNGP05.phx.gbl...
>I was thinking that there must have been a problem there but why can't I
>convert it to a string and have it more accurate? That isn't working
>either.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
> Due to a design flaw with Intel based chips, compute clock time is
> accurate to the nearest three thousandth of a second.
> This makes it difficult to coordinate precise times from a different time
> source to the times created by a Intel based computer. For example, an
> automated process control system have be recording times that are .003
> seconds 'off' what the computer thinks.
> You have to build in the .003 'fudge' factor.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Ellie" <nospam@.nospam.net> wrote in message
> news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
>|||Arnie Rowland (arnie@.1568.com) writes:
> Due to a design flaw with Intel based chips, compute clock time is > accurate to t
he nearest three thousandth of a second.

> This makes it difficult to coordinate precise times from a different
> time source to the times created by a Intel based computer. For example,
> an automated process control system have be recording times that are
> .003 seconds 'off' what the computer thinks.
Eh? It's perfectly possible in Windows to handle time down to
dissolution of 100 ns, I believe. At least, you can get far below
3.33 ms. For instance, in the SQL 2005 Profiler, you can opt to get
durations in s.
In fact, if you look at
http://manuals.sybase.com:80/online...=2862;lang=sv#X
you can see that Sybase has the same accuracy. And, as may know
Microsoft SQL Server was originally derived from Sybase. And Sybase
has its origin on Unix. So I doubt that whereever the design flaw
was, that it was with Intel chips.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You are seeing a limit of the DATETIME datatype in SQL Server. From
the Books on Line: "...to an accuracy of one three-hundredth of a
second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values
are rounded to increments of .000, .003, or .007 seconds..."
So, to store the time more accurately you must, as Aaron explained,
have to "roll your own".
Roy Harvey
Beacon Falls, CT
On Fri, 23 Jun 2006 14:13:22 -0400, "Ellie" <nospam@.nospam.net> wrote:

>I was thinking that there must have been a problem there but why can't I
>convert it to a string and have it more accurate? That isn't working either
.
>
>"Arnie Rowland" <arnie@.1568.com> wrote in message
>news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
>Due to a design flaw with Intel based chips, compute clock time is accurate
>to the nearest three thousandth of a second.
>This makes it difficult to coordinate precise times from a different time
>source to the times created by a Intel based computer. For example, an
>automated process control system have be recording times that are .003
>seconds 'off' what the computer thinks.
>You have to build in the .003 'fudge' factor.|||My brain checked out -it is a code artifact that predates Intel chip
design -is more in keeping of what I wanted to say. The latest IntelAMD
chips are capable of almost s precision.
Erlund, Thanks for catching this.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97EC62C58ABBYazorman@.127.0.0.1...
> Arnie Rowland (arnie@.1568.com) writes:
>
> Eh? It's perfectly possible in Windows to handle time down to
> dissolution of 100 ns, I believe. At least, you can get far below
> 3.33 ms. For instance, in the SQL 2005 Profiler, you can opt to get
> durations in s.
> In fact, if you look at
> http://manuals.sybase.com:80/online...=2862;lang=sv#X
> you can see that Sybase has the same accuracy. And, as may know
> Microsoft SQL Server was originally derived from Sybase. And Sybase
> has its origin on Unix. So I doubt that whereever the design flaw
> was, that it was with Intel chips.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||So does this mean that internally they are not rounded (0.00333) but what
I'm seeing in query analyzer when I try to query it, is the rounded .000,
.003 or .007, etc.? I'm not concerned about the accurate storing of the tim
e
but the query afterwards. If they are stored inaccurately, I should still be
able to use that query, unless it is stored internally pre-rounding. I still
don't get why I can't convert it to a string and do a string comparison
though. Hope I am making sense.
Thanks for all of your help. I'll read some of the links and work around
this.
Ellie
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:u21p9299dg97vdtin85imfs525fkefinc7@.
4ax.com...
> You are seeing a limit of the DATETIME datatype in SQL Server. From
> the Books on Line: "...to an accuracy of one three-hundredth of a
> second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values
> are rounded to increments of .000, .003, or .007 seconds..."
> So, to store the time more accurately you must, as Aaron explained,
> have to "roll your own".
> Roy Harvey
> Beacon Falls, CT
> On Fri, 23 Jun 2006 14:13:22 -0400, "Ellie" <nospam@.nospam.net> wrote:
>|||Effectively they are rounded (as demonstrated below) and stored that
way. Comparison of a string against a datetime is performed by
comparing datetime types after converting the string. The string
conversion follows the rules demonstrated below.
Lets look at them as strings, and as strings converted to datetime:
select '2006-06-16 09:04:39.338',
convert(datetime,'2006-06-16 09:04:39.338') UNION
select '2006-06-16 09:04:39.339',
convert(datetime,'2006-06-16 09:04:39.339') UNION
select '2006-06-16 09:04:39.340',
convert(datetime,'2006-06-16 09:04:39.340') UNION
select '2006-06-16 09:04:39.341',
convert(datetime,'2006-06-16 09:04:39.341') UNION
select '2006-06-16 09:04:39.342',
convert(datetime,'2006-06-16 09:04:39.342') UNION
select '2006-06-16 09:04:39.343',
convert(datetime,'2006-06-16 09:04:39.343') UNION
select '2006-06-16 09:04:39.344',
convert(datetime,'2006-06-16 09:04:39.344') UNION
select '2006-06-16 09:04:39.345',
convert(datetime,'2006-06-16 09:04:39.345') UNION
select '2006-06-16 09:04:39.346',
convert(datetime,'2006-06-16 09:04:39.346') UNION
select '2006-06-16 09:04:39.347',
convert(datetime,'2006-06-16 09:04:39.347') UNION
select '2006-06-16 09:04:39.348',
convert(datetime,'2006-06-16 09:04:39.348') UNION
select '2006-06-16 09:04:39.349',
convert(datetime,'2006-06-16 09:04:39.349') UNION
select '2006-06-16 09:04:39.350',
convert(datetime,'2006-06-16 09:04:39.350') UNION
select '2006-06-16 09:04:39.351',
convert(datetime,'2006-06-16 09:04:39.351') UNION
select '2006-06-16 09:04:39.352',
convert(datetime,'2006-06-16 09:04:39.352')
order by 1
2006-06-16 09:04:39.338 2006-06-16 09:04:39.337
2006-06-16 09:04:39.339 2006-06-16 09:04:39.340
2006-06-16 09:04:39.340 2006-06-16 09:04:39.340
2006-06-16 09:04:39.341 2006-06-16 09:04:39.340
2006-06-16 09:04:39.342 2006-06-16 09:04:39.343
2006-06-16 09:04:39.343 2006-06-16 09:04:39.343
2006-06-16 09:04:39.344 2006-06-16 09:04:39.343
2006-06-16 09:04:39.345 2006-06-16 09:04:39.347
2006-06-16 09:04:39.346 2006-06-16 09:04:39.347
2006-06-16 09:04:39.347 2006-06-16 09:04:39.347
2006-06-16 09:04:39.348 2006-06-16 09:04:39.347
2006-06-16 09:04:39.349 2006-06-16 09:04:39.350
2006-06-16 09:04:39.350 2006-06-16 09:04:39.350
2006-06-16 09:04:39.351 2006-06-16 09:04:39.350
2006-06-16 09:04:39.352 2006-06-16 09:04:39.353
I hope that helps.
Roy Harvey
Beacon Falls, CT
On Sat, 24 Jun 2006 07:48:43 -0400, "Ellie" <nospam@.nospam.net> wrote:

>So does this mean that internally they are not rounded (0.00333) but what
>I'm seeing in query analyzer when I try to query it, is the rounded .000,
>.003 or .007, etc.? I'm not concerned about the accurate storing of the tim
e
>but the query afterwards. If they are stored inaccurately, I should still b
e
>able to use that query, unless it is stored internally pre-rounding. I stil
l
>don't get why I can't convert it to a string and do a string comparison
>though. Hope I am making sense.
>Thanks for all of your help. I'll read some of the links and work around
>this.
>Ellie
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:u21p9299dg97vdtin85imfs525fkefinc7@.
4ax.com...
>

DATEDIFF in Report Builder

I'm having a bit of trouble with the DATEDIFF function in my Report Model project and in Report Builder.

I am trying to create a new Expression field that will work out a persons age from the current date and their Date of Birth, here is the formula that I have entered,

DATEDIFF("y", NOW(), DOB)

where DOB is the field that holds the persons date of birth in the database.

When I enter this into the formula box and click OK i get the following error,

"Operation is not valid due to the current state of the object."

The detailed error text is

Program Location:

at Microsoft.ReportingServices.Modeling.Expression.GetResultType()
at Microsoft.ReportingServices.ModelDesigner.ModelDesignerControl.m_ListNoItemExpression_Click(Object sender, EventArgs e)
at Microsoft.ReportingServices.ModelDesigner.ProjectModelView.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.DataWarehouse.VsIntegration.Designer.Host.CommandTargetMenuService.InvokeCommand(CommandID commandID)

This error ocurrs both in a Report Model project and also in report builder if I try to create a new field after the model has been deployed.

This also happens for DATEADD as well, I'm thinking it may be a problem with the first parameter but I can't find any reference on how to use this Function properly. Has anyone got either of these working in report builder, even if you don't could you post here to let me know you have the same problem.

Would be good to know i'm not alone in this.

Thanks|||I still really need help on this one.

If someone could load up Report Builder and try to create a new field using the DATEDIFF function this would really help me out.

If you get it working then i'll be able to take that onboard, if you don't get it working then I know i'm not alone.

Any help is appreciated|||

Report Builder? You mean Report Designer?
Why don't you get the age from the Query instead of getting it as a new field in the Report Desinger? I mean, add a calculated field directly to the Query, somehting like

SELECT [whatever you actually have], DATEDIFF(YEAR, GETDATE(), DOB) AS age
[and the rest of your query]

Or maybe I didn't understand your question

|||"When I enter this into the formula box"

Are you creating a field in the Repor Designer?
I wouldn't use NOW(), but Globals!ExecutionTime|||This issue has now been resolved, it was not occurring in Report Designer, it was occuring in Report Builder.

The DATEDIFF function in Report Builder must use the "long" names for the Interval and these must be capatalized.

E.g
"Day" - Will work
"dd" - Won't work
"day" - Won't work|||I have a similar question.

I can't seem to get the DATEDIFF function to work. I am trying to display the date from seven days prior to now.

My textbox has the value of...

=format(dateadd(Day, -7, Globals!ExecutionTime), "M/d")

and the error I get is...

Argument not specified for parameter 'DateValue' of Public Function Day(DateValue as Date) as Integer.

I just can't get my head around this one, and I'm sure it's simple. ANY help would be appreciated!|||Sorry, I meant to say I can't get the DateAdd function to work.|||

Day is a function, so it's expecting a parameter (a date value). I'd try to get that value from the query, not from a formula in a Text Box.
Maybe it's not the best solution, but I'd create a Dataset called DataSet1wkago with this query string:

SELECT DATEADD(DAY, -7, GETDATE()) AS last_week

And in the textbox I'd write

=First(Fields!last_week, "DataSet1wkago")

Again: this could be not the best solution, but it works.
I hope it helps you. Regards

|||Thanks, it's not the best solution, but it is still a solution!|||If you are trying to use this function in Report Builder then you will have to enclose the Interval in quotes e.g.

DATEADD("Day", -7, Globals!ExecutionTime)|||

I have the same problems with the data interval.

=Datediff("Day",Parameters!fromdate.Value,Now())

That I am using in a field in a Reporting Services report. I can not use the datediff in the query as I am using a parameter formattet at datetime.

Any suggestions on how this work or where to find useful documentation on expressions in Report Designer?

Thomas Black

|||

Hi,

try using "d" instead of "Day":

=Datediff("d", Parameters!fromdate.Value, now())

below are the list of 'code':

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week of year
h	Hour
n	Minute
s	Second

DATEDIFF in Report Builder

I'm having a bit of trouble with the DATEDIFF function in my Report Model project and in Report Builder.

I am trying to create a new Expression field that will work out a persons age from the current date and their Date of Birth, here is the formula that I have entered,

DATEDIFF("y", NOW(), DOB)

where DOB is the field that holds the persons date of birth in the database.

When I enter this into the formula box and click OK i get the following error,

"Operation is not valid due to the current state of the object."

The detailed error text is

Program Location:

at Microsoft.ReportingServices.Modeling.Expression.GetResultType()
at Microsoft.ReportingServices.ModelDesigner.ModelDesignerControl.m_ListNoItemExpression_Click(Object sender, EventArgs e)
at Microsoft.ReportingServices.ModelDesigner.ProjectModelView.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.DataWarehouse.VsIntegration.Designer.Host.CommandTargetMenuService.InvokeCommand(CommandID commandID)

This error ocurrs both in a Report Model project and also in report builder if I try to create a new field after the model has been deployed.

This also happens for DATEADD as well, I'm thinking it may be a problem with the first parameter but I can't find any reference on how to use this Function properly. Has anyone got either of these working in report builder, even if you don't could you post here to let me know you have the same problem.

Would be good to know i'm not alone in this.

Thanks|||I still really need help on this one.

If someone could load up Report Builder and try to create a new field using the DATEDIFF function this would really help me out.

If you get it working then i'll be able to take that onboard, if you don't get it working then I know i'm not alone.

Any help is appreciated|||

Report Builder? You mean Report Designer?
Why don't you get the age from the Query instead of getting it as a new field in the Report Desinger? I mean, add a calculated field directly to the Query, somehting like

SELECT [whatever you actually have], DATEDIFF(YEAR, GETDATE(), DOB) AS age
[and the rest of your query]

Or maybe I didn't understand your question

|||"When I enter this into the formula box"

Are you creating a field in the Repor Designer?
I wouldn't use NOW(), but Globals!ExecutionTime|||This issue has now been resolved, it was not occurring in Report Designer, it was occuring in Report Builder.

The DATEDIFF function in Report Builder must use the "long" names for the Interval and these must be capatalized.

E.g
"Day" - Will work
"dd" - Won't work
"day" - Won't work|||I have a similar question.

I can't seem to get the DATEDIFF function to work. I am trying to display the date from seven days prior to now.

My textbox has the value of...

=format(dateadd(Day, -7, Globals!ExecutionTime), "M/d")

and the error I get is...

Argument not specified for parameter 'DateValue' of Public Function Day(DateValue as Date) as Integer.

I just can't get my head around this one, and I'm sure it's simple. ANY help would be appreciated!|||Sorry, I meant to say I can't get the DateAdd function to work.|||

Day is a function, so it's expecting a parameter (a date value). I'd try to get that value from the query, not from a formula in a Text Box.
Maybe it's not the best solution, but I'd create a Dataset called DataSet1wkago with this query string:

SELECT DATEADD(DAY, -7, GETDATE()) AS last_week

And in the textbox I'd write

=First(Fields!last_week, "DataSet1wkago")

Again: this could be not the best solution, but it works.
I hope it helps you. Regards

|||Thanks, it's not the best solution, but it is still a solution!|||If you are trying to use this function in Report Builder then you will have to enclose the Interval in quotes e.g.

DATEADD("Day", -7, Globals!ExecutionTime)|||

I have the same problems with the data interval.

=Datediff("Day",Parameters!fromdate.Value,Now())

That I am using in a field in a Reporting Services report. I can not use the datediff in the query as I am using a parameter formattet at datetime.

Any suggestions on how this work or where to find useful documentation on expressions in Report Designer?

Thomas Black

|||

Hi,

try using "d" instead of "Day":

=Datediff("d", Parameters!fromdate.Value, now())

below are the list of 'code':

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week of year
h	Hour
n	Minute
s	Second