DB attach error

Hi,
When we attach one of our databases, sql server shows this error:
Error 3624:
Location: recorvery.c:2440
Expression: seenCkptEnd
ProcessId: 1388
Is there any way to recovery this database. Unfortunately we have no backup
:(
(I'm using sql server 2000 [service pack3 installed] and we have tried
attach a lot of pc/sql server)Sp_attach_db might not work if the database wasn't cleanly detached using
sp_detach_db. Since you have no backup, I suggest you contact Microsoft PSS
to help you salvage data. You might be able to rebuild the log using
unsupported techniques (a Google search can find these) but data integrity
will be questionable without proper database recovery.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dolphin" <d@.d.com> wrote in message
news:uGNKHitJEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> When we attach one of our databases, sql server shows this error:
> Error 3624:
> Location: recorvery.c:2440
> Expression: seenCkptEnd
> ProcessId: 1388
> Is there any way to recovery this database. Unfortunately we have no
backup
> :(
> (I'm using sql server 2000 [service pack3 installed] and we have tried
> attach a lot of pc/sql server)
>|||Hi,
This is most likely to be due to a bug in SQL Server. You could need to open
a case with Microsoft to get it resolved.
Thanks
Hari
MCDBA
"Dolphin" <d@.d.com> wrote in message
news:uGNKHitJEHA.1132@.TK2MSFTNGP12.phx.gbl...
> Hi,
> When we attach one of our databases, sql server shows this error:
> Error 3624:
> Location: recorvery.c:2440
> Expression: seenCkptEnd
> ProcessId: 1388
> Is there any way to recovery this database. Unfortunately we have no
backup
> :(
> (I'm using sql server 2000 [service pack3 installed] and we have tried
> attach a lot of pc/sql server)
>|||You can create a new database with the same name and filenames and
locations as the one you want to attach. Then stop SQL Server and replace
the new files with the files from the detached database. Start SQL Server.
If the database comes up suspect, put it in emergency mode and run DBCC
Checkdb on it. Depending on what Checkdb returns you may be able to salvage
the data by BCPing it out.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Day of week expression for parameter

I have a sales quotes report and want the report to run for quotes
entered 2 working days ago. I have used a default date-time parameter
in the past to do something similar for another report. I will be
running the report daily and elivering by subscrition, so the parameter
needs to be part of the report. In this case, I want the expression to
evaluate what day of the week it is today and calculate what the date
was 2 working days ago. For example:
If today is Monday, then get last Thursday's date = today -4 days.
If today is Tuesday, then use the range Friday to Sunday (in case
anything is entered over the weekend) = -4 to -2 days.
If today is Wednesday, then get Monday's date = today -2 days.
If today is Thursday, then get Tuesday's date = today -2 days.
If today is Friday, then get Wednesday's date = today -2 days.
Can anybody help me - I'm struggling with how to use the date functions
like this?
Thanks!agenda9533 had a similar question and the answer was writing a user function
Search using DATEDIFF and look for agenda9533 's posting

DateTime.Now expression expected problem

Hi - I'm using VWD, VB, and created a dataset/tableadapter to insert a record into a SQL Express database. The database has a couple of columns, but specifically a Datetime column.

Using the default insert created, I have the following code:

Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapter
Profile.partyid = da.Insert(Profile.UserName, tbName.Text, DateTime.Now)

The compiler throws an error though, saying 'Expression expected' - and it squiggles an underline under the closing bracket after DateTime.Now - I have no problem if I'm trying to update a record using:

Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapter
Dim pd as partyDetails.partyDetailsDataTable
pd = da.GetPartyDetailsByID(Profile.partyid)
da.Update(Profile.UserName, tbName.text, DateTime.Now, Profile.partyid, Profile.partyid)

Have I an error in my Insert section?

Thanks for any help,

Mark

Look at what the functions da.Insert and ds.Update are expecting as their arguments.

Maybe daInsert is expecting the date as a string instead of a DateTime object

Datetime Parameter Format

Hi,all
I have a datetime parameter,
I use calendar to select value,but I want to format it as "yyyy-MM"
Any suggest?Can I use expression,how to ?
Kevin ChuJust get the value in as datetime format and use
format(Fields!xxx.Value,"yyyy-MM")
--
Tom Stude
"Kevin" wrote:
> Hi,all
> I have a datetime parameter,
> I use calendar to select value,but I want to format it as "yyyy-MM"
> Any suggest?Can I use expression,how to ?
> Kevin Chu
>|||=?Utf-8?B?VG9t?= <membership@.stude.no> wrote in news:ADC758F7-F6A7-4F56-
8C5C-1BAD643ABF30@.microsoft.com:
I want to show "yyyy-MM" format in preview,not get the value
> Just get the value in as datetime format and use
> format(Fields!xxx.Value,"yyyy-MM")
>

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

DateDiff Function, VERY IMPORTANT!!

I have 2 dates one a parameter and one from a field, I need to calculate the
number of days inside a reporting services expression window. The Datediff
function does not seem to work, I cannot do it on the SQL Query because one
of the dates is from a parameter (ie. @.date)You can use the datediff in your query.
SELECT DATEDIFF(day, pubdate, @.date) AS no_of_days
FROM titles where pubdate > @.date
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DragonVic" <DragonVic@.discussions.microsoft.com> wrote in message
news:AF5A3BFA-C95D-4967-B226-16CE4B4A69EB@.microsoft.com...
>I have 2 dates one a parameter and one from a field, I need to calculate
>the
> number of days inside a reporting services expression window. The Datediff
> function does not seem to work, I cannot do it on the SQL Query because
> one
> of the dates is from a parameter (ie. @.date)|||You can also do it in the expression using VB datediff ie
=datediff(DateInterval.Day,Parameters!myparm.Value,Today())
I prefer doing this stuff in sql like Mike does tho...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DragonVic" <DragonVic@.discussions.microsoft.com> wrote in message
news:AF5A3BFA-C95D-4967-B226-16CE4B4A69EB@.microsoft.com...
>I have 2 dates one a parameter and one from a field, I need to calculate
>the
> number of days inside a reporting services expression window. The Datediff
> function does not seem to work, I cannot do it on the SQL Query because
> one
> of the dates is from a parameter (ie. @.date)

DateDiff doesn'twork

I must do a report using this expression for data set:
= "SELECT * FROM Partitario WHERE DATEDIFF(d,DataScadenza, " &
Parameters!Data.Value &") > 0"
This is a part of the complete query.
I have a date as a input parameter. The field named "DataScadenza" belongs
to table "Partitario".
I have seen that this query doesn't extract anything.
The value for Datediff is always negative.
When I change the expression rendering it completely as Transact-SQL query,
I obtain the right results; but I cannot this query, it is a bit of the
complete query.
Which is the reason of this behaviour?
Many thanks for your suggestsI spend 75% of my time writing these expressions against a db2 database and
do not have the luxury of using the complete set of tsql statements. I am
bound my iseries odbc connection and what it can work with for statements.
What datasource are you working against?
Michael
"Pasquale" wrote:
> I must do a report using this expression for data set:
> = "SELECT * FROM Partitario WHERE DATEDIFF(d,DataScadenza, " &
> Parameters!Data.Value &") > 0"
> This is a part of the complete query.
> I have a date as a input parameter. The field named "DataScadenza" belongs
> to table "Partitario".
> I have seen that this query doesn't extract anything.
> The value for Datediff is always negative.
> When I change the expression rendering it completely as Transact-SQL query,
> I obtain the right results; but I cannot this query, it is a bit of the
> complete query.
> Which is the reason of this behaviour?
> Many thanks for your suggests
>|||I use a native ms sql server connection.
"MJP-tennis" wrote:
> I spend 75% of my time writing these expressions against a db2 database and
> do not have the luxury of using the complete set of tsql statements. I am
> bound my iseries odbc connection and what it can work with for statements.
> What datasource are you working against?
> Michael
> "Pasquale" wrote:
> > I must do a report using this expression for data set:
> > = "SELECT * FROM Partitario WHERE DATEDIFF(d,DataScadenza, " &
> > Parameters!Data.Value &") > 0"
> > This is a part of the complete query.
> > I have a date as a input parameter. The field named "DataScadenza" belongs
> > to table "Partitario".
> > I have seen that this query doesn't extract anything.
> > The value for Datediff is always negative.
> > When I change the expression rendering it completely as Transact-SQL query,
> > I obtain the right results; but I cannot this query, it is a bit of the
> > complete query.
> >
> > Which is the reason of this behaviour?
> >
> > Many thanks for your suggests
> >|||Are you sure that your parameter Date is in the correct date format?
Check what comes through by adding a textbox which displays the date on your
report, to see that it's not being converted in any way.
And then do a sql convert in your query, to make sure the date formats are
the same:
= "SELECT * FROM Partitario WHERE DATEDIFF(d,DataScadenza, convert(datetime,
" & Parameters!Data.Value &", 104)) > 0"
Change 104 to the ID of the date format you need to use. (Look at "Cast and
Convert" in SQL Server books online.)
Kaisa M. Lindahl Lervik
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:4EE0272F-CE1A-43AB-8F6F-E1D4F9D3B4A1@.microsoft.com...
>I must do a report using this expression for data set:
> = "SELECT * FROM Partitario WHERE DATEDIFF(d,DataScadenza, " &
> Parameters!Data.Value &") > 0"
> This is a part of the complete query.
> I have a date as a input parameter. The field named "DataScadenza" belongs
> to table "Partitario".
> I have seen that this query doesn't extract anything.
> The value for Datediff is always negative.
> When I change the expression rendering it completely as Transact-SQL
> query,
> I obtain the right results; but I cannot this query, it is a bit of the
> complete query.
> Which is the reason of this behaviour?
> Many thanks for your suggests
>

DateAdd narrowing conversion error

I am simply trying to take a date and add the value from another field to it using the following expression:

=dateadd("d", " & Fields!new_tgddatefrom.Value & ", Parameters!HolidayDate.Value)

new_tgddatefrom.Value is an int data type
HolidayDate.Value is a date

The expression works fine if I use an acutal number for the value to add but when I use the field.value I get an error:

Overload resoution failed because no accessible 'DateAdd can be called without a narrowing conversion.

Why or why is it so difficult to work with dates!?!? If anyone can help me get this working I would sincerely appreciate it!

Debra

Debra:

The multiplier in the dateadd function must be an integer and since you say the field value is one why concatenate the quotes?

=dateadd("d", Fields!new_tgddatefrom.Value, Parameters!HolidayDate.Value)

Petr
|||Dear
Plz check date type of date interval, both date should be date type.
Thanks
Manoj Singh

dateadd in expression error

I have the following in a textbox expression:
=datepart("d",dateadd("d", 3, @.startDate))
I have this or something similar in many textboxes and they are all
returning the following error:
The value expression for the textbox â'textbox24â' contains an error:
[BC30037] Character is not valid.
Any ideas? Please help!Replace @.startDate with Parameters!StartDate.Value
GeoSYnch
"SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
news:CC20E91E-80CC-4CF0-AB8F-D02FCD513B73@.microsoft.com...
>I have the following in a textbox expression:
> =datepart("d",dateadd("d", 3, @.startDate))
> I have this or something similar in many textboxes and they are all
> returning the following error:
> The value expression for the textbox 'textbox24' contains an error:
> [BC30037] Character is not valid.
> Any ideas? Please help!
>
>|||THANK YOU!!!!!!
"GeoSynch" wrote:
> Replace @.startDate with Parameters!StartDate.Value
>
> GeoSYnch
>
> "SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
> news:CC20E91E-80CC-4CF0-AB8F-D02FCD513B73@.microsoft.com...
> >I have the following in a textbox expression:
> >
> > =datepart("d",dateadd("d", 3, @.startDate))
> >
> > I have this or something similar in many textboxes and they are all
> > returning the following error:
> >
> > The value expression for the textbox 'textbox24' contains an error:
> > [BC30037] Character is not valid.
> >
> > Any ideas? Please help!
> >
> >
> >
>
>

Dateadd Function in ReportViewer

Hi,

I need to perform dateadd function on one of my columns in my report.

So I assigned an expression to that column which goes like this.

=Dateadd(DateInterval.Hour,Fields!InitialDrive.Value,Fields!EarliestArrivalTime.Value)

It doesnt look like working... It comes out in the report as #ERROR

Where am i going wrong?

Regards,

Lavanya.

you need 3 arguments

DATEADD(datepart,number,date)

Arguments

DatepartAbbreviationsYearyy, yyyyquarterqq, qMonthmm, mdayofyeardy, yDaydd, dWeekwk, wwHourhhminutemi, nsecondss, smillisecondms

|||

Can you do the DATEADD in the stored procedure that you run?

|||

Yeah... I have 3 arguments

|||

Yeah... I can go with Stored procedures... But I am not sure how to add decimal number

DATEADD(hh,1.25,10/22/2007 8:00 AM) and it shud give me 10/22/2007 9:15 AM

Any idea how to do it...

|||

The commands
DECLARE @.TEST DATETIME
SET @.TEST = CONVERT(DATETIME,'10/22/2007 8:00 AM')
PRINT @.TEST
PRINT DATEADD(hh, 1.25, @.TEST)
PRINT DATEADD(mi, (1.25 * 60), @.TEST) -- note use of mi (orminute)
give
Oct 22 2007 8:00AM
Oct 22 2007 9:00AM
Oct 22 2007 9:15AM -- The answer you require!

|||

Yes! the DateInterval.xxx is units of the integer of the second argument (Tricky)

DateAdd expression works in tsql but doesn't work in ssis

Hi There,

I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?

dateadd("dd", -1, datediff("dd", 0, getdate()))

Thanks.

Some more info please. What do you mean by "it doesn't work"? Do you get an error or the wrong result?

If the latter, tell us what you result you get and also what result you are expecting to get.

Thanks

-Jamie

|||DateDiff returns an integer while DateAdd expects a datetime in that position. T-SQL is able to implicitly cast dates to integers, while SSIS cannot.

|||

Ok..If you run the below query in query analyzer..

select dateadd("dd", -1, datediff("dd", 0, getdate()))

it gives me.."2007-05-08 00:00:00.000". I would like to get the same value in ssis. In ssis, if I use the above as an expression for a variable, I get a design time error. "The expression for variable failed evaluation, there was an error in the expression".

Thanks for responding.

|||

Ok..you are right..so can i cast it like this..

dateadd("dd", -1, (DT_DBTIMESTAMP)(datediff("dd", 0, getdate()))). This doesn't work either. How do I cast it?

Thanks.

|||

Sam_res03 wrote:

Ok..you are right..so can i cast it like this..

dateadd("dd", -1, (DT_DBTIMESTAMP)(datediff("dd", 0, getdate()))). This doesn't work either. How do I cast it?

Thanks.

You'd have to use DateAdd to perform the cast from integer to date and thus define 0 as 1/1/1900 the way T-SQL does.

dateadd("dd", -1,
dateadd("dd",
datediff("dd",
dateadd("dd",0,(DT_DBDATE)"1/1/1900")
, getdate())
,(DT_DBDATE)"1/1/1900")
)

|||

Hi Jay,

Thanks for your reply. I really appreciate it. Event though your sol works, I thought I would use this instead..

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 00:00:00") as this was much readable. I am sure this works for all situations.

So

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 00:00:00")

gives 5/8/2007 00:00:00

and

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 23:59:59")

gives 5/8/2007 11:59 PM

I am not sure which one is efficient though, probably yours...

Thanks

DateAdd

Trying to use the DateAdd feature so I can do some MTD/YTD
comparisons. When I use the Expression builder, I am getting errors.
Using =DateAdd(yy, -1, 'parameter'), the yy is underlined in red.
I've tried year and Year. I've even seen it put into quotes ("yy").
I'm basically just trying to take the current date and subtract 1 year
from it. My ultimate goal is to create a report where I get LYTD/YTD
and MTD compared to the same month a year ago. This would key of a
report parameter since the user may want to look at particular month,
not just current. The report works fine as long as you manually put
in every date. I just need the DateAdd to make some automatic
calculations for me. Thanks in advance.On Dec 5, 3:51 pm, Barry <ba...@.tecelectric.com> wrote:
> Trying to use the DateAdd feature so I can do some MTD/YTD
> comparisons. When I use the Expression builder, I am getting errors.
> Using =DateAdd(yy, -1, 'parameter'), the yy is underlined in red.
> I've tried year and Year. I've even seen it put into quotes ("yy").
> I'm basically just trying to take the current date and subtract 1 year
> from it. My ultimate goal is to create a report where I get LYTD/YTD
> and MTD compared to the same month a year ago. This would key of a
> report parameter since the user may want to look at particular month,
> not just current. The report works fine as long as you manually put
> in every date. I just need the DateAdd to make some automatic
> calculations for me. Thanks in advance.
DateAdd is very finicky, and the documentation is really poor. It's
case sensitive, and only certain combinations seem to work. You will
want to use yyyy for Year, M or m for Month, d for Day, H for hour, n
for Minute, s for Second.
You want to not use the double quotes when you are using the function
in SQL, but do use the quotes when it is in a .Net expression.
1 Year Ago from Date
= DateAdd( "yyyy", -1, Parameters!CurrentYearsDate.Value )
-- Scott|||On Dec 5, 3:30 pm, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 5, 3:51 pm, Barry <ba...@.tecelectric.com> wrote:
> > Trying to use theDateAddfeature so I can do some MTD/YTD
> > comparisons. When I use the Expression builder, I am getting errors.
> > Using =DateAdd(yy, -1, 'parameter'), the yy is underlined in red.
> > I've tried year and Year. I've even seen it put into quotes ("yy").
> > I'm basically just trying to take the current date and subtract 1 year
> > from it. My ultimate goal is to create a report where I get LYTD/YTD
> > and MTD compared to the same month a year ago. This would key of a
> > report parameter since the user may want to look at particular month,
> > not just current. The report works fine as long as you manually put
> > in every date. I just need theDateAddto make some automatic
> > calculations for me. Thanks in advance.
> DateAddis very finicky, and the documentation is really poor. It's
> case sensitive, and only certain combinations seem to work. You will
> want to use yyyy for Year, M or m for Month, d for Day, H for hour, n
> for Minute, s for Second.
> You want to not use the double quotes when you are using the function
> in SQL, but do use the quotes when it is in a .Net expression.
> 1 Year Ago from Date
> =DateAdd( "yyyy", -1, Parameters!CurrentYearsDate.Value )
> -- Scott
PERFECT!!! I have spent many numerous hours searching for the correct
syntax. Worked great! Thanks so much.