Date Query Parameter

Hi There,

I've only just starting working with RSS2005. I've started designing basic reports that pulls info from our HEAT DB (incident/change logging system).

One of the report requirements is to be able to pull reports for calls that were logged for specific day/date, using the calllog.recvddate field in the HEAT DB.

What i would like to know is how do i do this ? i.e. how do i setup the date query parameter so that the user can select a date from the calender picker that returns all calls logged for the selected date ? How do i link the date parameter to the calllog.recdvddate field ?

Thanks,

RSS2005 NOOB

Hello,

Here's what you need to do.

Select 'Report Parameters' from the Report menu. Click 'Add', give the parameter a name (ex. DateParam), change the data type to 'DateTime', and hit OK. Go to your query from the Data view, and in your where clause, add calllog.recvddate = @.DateParam. If your recvddate has the time stored as well, you'll need to add this instead: convert(varchar, calllog.recvddate, 101) = @.DateParam.

Now when you preview your report, you will see a parameter section at the top of your report, pick your date and select 'View Report'.

Hope this helps.

Jarret

|||

Thanks so much for the Reply Jarret, i tried what u said and it apprears to work. The call totals correspond to those in the DB .... UBBER YAY !!!!!

Can't believe how close i was yet so far at the same time !

date parmeter converts into drop down

I am working in SSRS 2005. I have three parameters on the reports

Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...

2nd and 3rd parameters are fromdate and todate which are datetime parameters.

When user select let's say MTD from parameter1 then i have Stored procedure which populate the fromdate paramter with 1st date of the month and, todate populate the todays's date. The problem i am facing is the moment these date parameters get populated it converts into drop down. i want these date parameters should be still datetime so user can select the date. The value i am passing to these parameters are datetime type( Now()) .Still the date parameters controls are showing as dropdown. I don't know how to handle it. Please help me if you have faced this kind of problem.

Thanks,

Ranjay

Dropdown appears because parameter's "available values" option is set. In this case, it is not possible to enter value. Try to set default value instead of available values.|||Lev,thanks for your reply. I know that it will work for default but i want to populate the date parameters based on some other parameters selcted in report. Default value just shows one time. it doesn't change the value next time when i change the other parameter.|||Default value can be query based.|||

That's true but problem with default value is it's not getting refreshed when my other parameter change. Default value is set only the first time when it initialize. I don't know if i was able to explain the problem properly

|||

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1

date parmeter converts into drop down

I am working in SSRS 2005. I have three parameters on the reports

Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...

2nd and 3rd parameters are fromdate and todate which are datetime parameters.

When user select let's say MTD from parameter1 then i have Stored procedure which populate the fromdate paramter with 1st date of the month and, todate populate the todays's date. The problem i am facing is the moment these date parameters get populated it converts into drop down. i want these date parameters should be still datetime so user can select the date. The value i am passing to these parameters are datetime type( Now()) .Still the date parameters controls are showing as dropdown. I don't know how to handle it. Please help me if you have faced this kind of problem.

Thanks,

Ranjay

Dropdown appears because parameter's "available values" option is set. In this case, it is not possible to enter value. Try to set default value instead of available values.|||Lev,thanks for your reply. I know that it will work for default but i want to populate the date parameters based on some other parameters selcted in report. Default value just shows one time. it doesn't change the value next time when i change the other parameter.|||Default value can be query based.|||

That's true but problem with default value is it's not getting refreshed when my other parameter change. Default value is set only the first time when it initialize. I don't know if i was able to explain the problem properly

|||

Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1

sql

date IS NULL not working......?

Hi,

I have a problem with a SQL that check if a date column IS NULL.
On one server the check work ok but on another (the same data is on both - restored copy) the check does not find any NULL values.
If check where datecolumn =convert(datetime,'9999-12-31 23:59:59.000',121) I get the same result as when checking for NULL in the other.

Is there any parameter set somewhere that tell the server to return a value even if NULL is stored in the database?

thank you for reading,
YakoBayIs there any parameter set somewhere that tell the server to return a value even if NULL is stored in the database?

I'd be very surprised.|||Have you tried ISNULL( column, 'default value' )?|||See COALESCE in BOL.

Date grouping

Hello All,

Noob here. Trying to group by date in report and it is not working as expected. The date fields include a date/times i.e. 12/23/2006 9:45:00 AM. I can change the date properties to make the data appear as 12/23/2006, but when I group on date, it returns multiple rows showing the same date instead of just one row with the one date. The data is aggregated for the grouping so it seems like it should work properly. Here is an example of what I am trying to do.

Current...

Date Data

12/23/2006 9:45:00 AM 12

12/23/2006 10:00:00 AM 8

12/23/2006 10:15:00 AM 5

Want it to be...

Date Data

12/23/2006 25

Using SQL Server 2000 and RS2000

Thanks,

Clint

Try

=DateValue(Fields!Datetime.Value)

in the Edit / Expression for your Datetime group. I think this should get you what you wanted.

Ham

|||

Thank Ham

Would this work if the field is =Fields!DateTimeStamp.Value?

|||Nothing works like trying, yep it worked. thanks so much!|||

xtrout,

I glad that is worked for you. Can you mark this answers so that others can view our solution.

Thanks

Ham

date function in C#

hi experts,

i'm working in a web page for some statistics and i have a calendar where the customer can choose a day a week or a month and according to the date he select i need to query the database according to the date selected.

what i want to know is how can i store the date for a day in a variable so i can call it from a stored procedure, the day actually is easy what i want is how can i store the whole week in a variable so i can give it to the stored procedure and query the data in the database according to the whole week may be with startday and endday

also the same problem for the whole month, any idea how can i implement that in C#?!!!

thanks

Are you asking how would you query by a date range? such as a week?

-c

|||

thanks for your reply, what i wnat is how can i store the value of the whole week and month in a variable. i know how to query the database. the situation is as follow:

i have a web site where a client can select a day week or a month to see some statistics, for the day i know how to do it. and i have also stored procedures. what i want is how to store the startday and end day in a variable when the client select a week or a month, so i can assign these variables to the stored procedure so the stored procedure can query the database and return the data for just the specific selected date.

i hope i made my self clear.

any help will be appreciated.

thanks

|||

mrjoka

Check out the article below and look for the sample function called RunStoredProcParams here you will see how to return a datareader from a stored procedure that accepts a parameter

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

HTH

|||

thanks for the link, actually what i want is how to store the date value of the selected week or month from a callendar in a variable so i can assing them to a stored procedure. i know how to assign parameters to SP but how can i store the startdate and enddate selected from the user from the callendar? for example if the user select a week or a month for a day i can use :date1 = Calendar1.SelectedDate.ToShortDateString(); but for a week or a month!!!!

this is what makes me crazy these days

thanks in advance

|||

Since the SelectedDate is a DateTime variable you can do this for month;

int SelectedMonth = Calendar1.SelectedDate.Month;

The week can be done as follows

System.Globalization.Calendar cal = System.Globalization.CultureInfo.CurrentCulture;int SelectedWeek = cal.GetWeekOfYear;

|||

thanks for the reply it realy helps but when i tried the week i had these fouts:

Error 1 Cannot implicitly convert type 'System.Globalization.CultureInfo' to 'System.Globalization.Calendar'

Error 2 Cannot convert method group 'GetWeekOfYear' to non-delegate type 'int'. Did you intend to invoke the method?

any idea why?

thanks again

|||

yes, I forgot the .Calendar on the CultureInfo - so its = System.Globalization.CultureInfo.CurrentCulture.Calendar;

System.Globalization.Calendar cal = System.Globalization.CultureInfo.CurrentCulture.Calendar;

That will fix both issues.

-c

|||

thanks for the quick reply,

i did wat you told me and i get rid of the first fout but the second one is still there

any idea

thanks

|||

my bad - you need to add () to the call;

int SelectedWeek = cal.GetWeekOfYear();

|||

sorry to bother you again,

here is the fout this time:

Error 1 No overload for method 'GetWeekOfYear' takes '0' arguments

is it because i'm using it inside a case statement?!!!

thanks

|||

here you go;

int SelectedWeek = System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(Calendar1.SelectedDate, System.Globalization.CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday);

-c|||

do i need to declareDayOfWeek because it gave me this error:

Error 1 The name 'DayOfWeek' does not exist in the current context

thanks

|||

thanks man it works like a charm

|||

DayOfWeek is under System and is an enumeration so it should be fine. Maybe try System.DayOfWeek.

-c

Date format with SQL Server 2000

Hi,

I'm working with a table with more than 2 million rows. The problem is that the table has a field called "ShipDate" of type nvarchar. When I try to change the data type to DateTime sql server throws a data conversion error.

I noticed that the date format is as follow: 2006-10-23, so, is there a way sql server takes this field and change it using the field values and applying the correct format? meaning: 10/23/2006

thanks a lot

The mentioned value should be easily converted into a datetime column (2006-10-23). You seem to have corrupt data stored in the datecolumn. Try to get rid of it and prior identify it by using the following query:

SELECT Datecolumn
FROM SomeTable
WHERE ISDATE(Datecolumn) = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Date Format Problem

i have change my field like that format

= format(Fields!TimeStart.Value, "dd -MMM- yyyy")

Its working perfectly.Once i deployed my report this format is not working

once deploy my report its showing that field fill dd -MMM- yyyy why?there is no date in that field?

i dont understand can anybody know this one.

From the description it sounds like the TimeStart field is of type DateTime when running in preview (running based on the cached data from the .rdl.data file), but of type string when running on the report server when running against live data.

Are you running against the same data source in your design environment and the server?

Did you click the little green refresh icon in the preview toolbar (this will update the cached .rdl.data file kept by report designer)?

-- Robert

|||

thanx for your reply

its giving same problem.Its actually live data.Its giving once deploy my report only.

Date format isn't working

Hello, all. We've got a table that holds the begin and end date for allowing
people into a voting app. Here's the DDL,
CREATE TABLE [tblVotingPeriod] (
[pk] [tinyint] IDENTITY (1, 1) NOT NULL ,
[beginVote] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_beginVote]
DEFAULT (getdate()),
[endVote] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_endVote]
DEFAULT (getdate()),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_dateAdded]
DEFAULT (getdate()),
CONSTRAINT [PK_tblVotingPeriod] PRIMARY KEY CLUSTERED
(
[pk]
) ON [PRIMARY]
) ON [PRIMARY]
GO
We need it to store "3/15/2005 12:00:00 AM" as the beginVote and "3/18/2005
12:00:00 AM" as the endVote. However, each time we try entering the
"12:00:00 AM" part, SQL Server ignores it. When we retrieve the date, it
won't display the time in the ASP page -- just the date. We've tried using
the CONVERT(smalldatetime, beginVote,109) to no avail. Any ideas? Thanks
much."dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:ekmwoibGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> 12:00:00 AM" as the endVote. However, each time we try entering the
> "12:00:00 AM" part, SQL Server ignores it. When we retrieve the date, it
Sounds like a problem with your client code, not SQL Server. SQL Server
DATETIME and SMALLDATETIME datatypes must have a time component -- they
can't be ignored. Or are you getting an error of some sort?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Even in SQL Server when viewing the table in Enterprise Mgr., it shows the
date as "3/15/2005" without the "12:00:00 AM" part. Each time I type it in
and tab out, it ignores it. Is there something special about 12:00:00 AM? Is
that the "default" for the date, so it doesn't even show it? What if you do
want it shown?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O7BswobGFHA.544@.TK2MSFTNGP12.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:ekmwoibGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Sounds like a problem with your client code, not SQL Server. SQL
> Server
> DATETIME and SMALLDATETIME datatypes must have a time component -- they
> can't be ignored. Or are you getting an error of some sort?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Even in SQL Server when viewing the table in Enterprise Mgr., it shows the
> date as "3/15/2005" without the "12:00:00 AM" part. Each time I type it in
> and tab out, it ignores it. Is there something special about 12:00:00 AM?
Is
> that the "default" for the date, so it doesn't even show it? What if you
do
> want it shown?
EM does appear to truncate the date if the time is 12:00:00 AM. And
yes, that is the "default" time -- if you insert a row with '20050315' and
no time component, the time will automatically be set to 12:00:00 AM.
(Actually, 00:00:00). You should be able to format the date to see the time
component in ASP using VBScript's date formatting functions. It is being
returned by SQL Server, you're just not displaying it. Note also, Query
Analyzer does not trucate the date.
One side note, by the way: You should re-consider that date format, as
it's ambiguous (it can change with locale); for example, what does the
following represent: '01/02/2005' ? Depends on what country you're in.
The preferred format in SQL Server is YYYYMMDD.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks, Adam. I just noticed that it took 12:01:00 AM, so 12:00:00 am is
just not being shown. Do you know of a less ambigious date format? Thanks.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Is
> do
> EM does appear to truncate the date if the time is 12:00:00 AM. And
> yes, that is the "default" time -- if you insert a row with '20050315' and
> no time component, the time will automatically be set to 12:00:00 AM.
> (Actually, 00:00:00). You should be able to format the date to see the
> time
> component in ASP using VBScript's date formatting functions. It is being
> returned by SQL Server, you're just not displaying it. Note also, Query
> Analyzer does not trucate the date.
> One side note, by the way: You should re-consider that date format, as
> it's ambiguous (it can change with locale); for example, what does the
> following represent: '01/02/2005' ? Depends on what country you're in.
> The preferred format in SQL Server is YYYYMMDD.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:e9lQX6bGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Thanks, Adam. I just noticed that it took 12:01:00 AM, so 12:00:00 am is
> just not being shown. Do you know of a less ambigious date format? Thanks.
YYYYMMDD HH:MM:SS - '20050315 00:00:00'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Which format do I use if I want the date to come out "Mar 21 2005"? Thanks.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Is
> do
> EM does appear to truncate the date if the time is 12:00:00 AM. And
> yes, that is the "default" time -- if you insert a row with '20050315' and
> no time component, the time will automatically be set to 12:00:00 AM.
> (Actually, 00:00:00). You should be able to format the date to see the
> time
> component in ASP using VBScript's date formatting functions. It is being
> returned by SQL Server, you're just not displaying it. Note also, Query
> Analyzer does not trucate the date.
> One side note, by the way: You should re-consider that date format, as
> it's ambiguous (it can change with locale); for example, what does the
> following represent: '01/02/2005' ? Depends on what country you're in.
> The preferred format in SQL Server is YYYYMMDD.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||Thanks, Adam. I got it working, thanks to your help! I had a mistake in my
code where I was doing the convert -- instead of converting it to a char or
varchar, I was changing it to a smalldatetime, which it already was! I'm
using the 100 format to convert it to this: Mar 15 2005 12:00AM
Thanks for your help :) Much appreciated.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:e6D5c9bGFHA.2416@.TK2MSFTNGP14.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e9lQX6bGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> YYYYMMDD HH:MM:SS - '20050315 00:00:00'
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||I suggest you check out my article on the subject:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message news:eTbYl9bGFHA.2356@.TK2MSFTNGP12.phx.g
bl...
> Which format do I use if I want the date to come out "Mar 21 2005"? Thanks
.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
>

Date Format Display Convert US to International

Hi,
I am working on a Report that needs to display dates in International Format
(UK) dd/mm/yy, the database and Sql RS are installed on a US date format
mm/dd/yy, how do I get the date to display correctly in the Report?
Cheers
Bobselect the textbox and properties and go to format and select custom and
give your format e.g dd-mm-yyyy
Amarnath
"Bob" wrote:
> Hi,
> I am working on a Report that needs to display dates in International Format
> (UK) dd/mm/yy, the database and Sql RS are installed on a US date format
> mm/dd/yy, how do I get the date to display correctly in the Report?
> Cheers
> Bob
>|||Amarnath,
Thanks, had tried that. Found you need to change Language in
Properties/International/Launguage to English (United
Kingdom).............
Bob
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:D6405D61-4847-43D3-AD6E-E7C396BCE7DC@.microsoft.com...
> select the textbox and properties and go to format and select custom and
> give your format e.g dd-mm-yyyy
> Amarnath
> "Bob" wrote:
>> Hi,
>> I am working on a Report that needs to display dates in International
>> Format
>> (UK) dd/mm/yy, the database and Sql RS are installed on a US date format
>> mm/dd/yy, how do I get the date to display correctly in the Report?
>> Cheers
>> Bob

Date Format

Hi everyone, I need help with the date formatting in ssrs 2000. I'm writting reports with date ranges, specific dates, and all are working fine. Now, I was asked to create a report that shows current month, a different one for the QTR, and and also for the current year. I've tried different formats, keep gettin stuck where it doesnt return data. Please help.

Abner

Are you using stored procedure? where do you need a help? in SQL or Report designer, please give me some more details.

|||

I need help in report designer. I was able to run my report and get current dates, GETDATE(), but when it comes to current month, I've tried different ways but it dont work. So, I'm seeking as much help as possible. I'm still new at reporting service, but eager to learn as much as possible.

Thanks advance

Abner

|||

Hi, Abner:

IDoes this meet your needs?

Month(ToDay())

Year(ToDay())

|||

Hi REX,

Thanks for the reply, but I have tried those formats and i just get a blank page. Well, when I type MONTH(TODAY()) it dont reconize today, so I wrote MONTH(GETDATE()), and it just returns a blank page...ive tried it with =DatePart(m, GETDATE()), also =DATEADD(m, 0, GETDATE()), and DatePart("m", field!Promise_Date.Value). and it still just give me a blank page. Is it because I also have it divided by week? even thought I test it without breaking it into weeks. Pleasssssssssseeeeeeeeee help. I dont know how else to do it.

Abner

|||

Hi Abner,

Date related functions in report viewer are not same as we have in dot net, they have some weired behavior, so we cant not use abbreviated Dateparts like "mm", "m" or "d". Check this post for more info on this -

http://blogs.msdn.com/bimusings/archive/2005/09/13/464836.aspx

Date Concatenation

Working with an archaic mess of Access dbs, and am trying to convert to a single SQL Server 2005 db.

A couple of places in the original monstrosity I have the time and date in separate columns, like the following example:

[Ticket Date] [Ticket Time]
12/1/99 0845
12/2/01 0932

One problem is the two columns can be of different data types, like a date col and a text time. If anyone knows how to bring these into a single column using SSIS, I would love to know since I have been battling with it for a while.

Use the Derived Column component. Cast them as strings and then its just a string a manipulation problem.

-Jamie

date calculations using a time hierarchy

Hi There

I am a relative newbie at SSAS 2005, but I've created a working cube and populated it with data.

I have a fact table with three date fields:
[record date]
[start date] - date the product started
[end date] - date the product was withdrawn
+ some other data fields
I use the [record date] to analyse my data with a server-populated time dimension. So far so good, it works fine.
However I need to look at how many days the products were active during the current time period. For exampe, if I'm in Q1 of 2005 and my product ran from 1st december 2004 to 15th january 2005, the answer is 15 days. This needs to be totaled across various other dimensions too.
I have tried a lot of different ways of doing it without much success. Any ideas on how to implement something like this?

ThanksCould you explain the schema, and the granularity of the fact table - for example, is there only 1 record per product, or 1 record per sales transaction? In the latter case, shouldn't the product start and end dates be associated with a Product dimension, rather than with each transaction?|||It's a simplified proof-of-concept project, so I can understand how it works before trying it out on the full database which is more complex.
These are financial products which have a start and end date. There is one record per product and they are to be analysed by various dimensions, for example product type, which would be the maximum granularity (not the products themselves). I also need to look at them by other time dimensions, for example when the records appear in the database, which may be any time after they have actually incepted. So I could have product type on rows and time on columns, and the cells would be the sum of all the days of activity for a given product type for that time period. Or I could have 'appearence time' on rows, so each cell would be the sum of active days for products during the colum time, for all products appearing in a given time frame.
Hope this is clear enough? I realise this is maybe a bit general but I'm only after a general approach strategy rather than an in-depth analysis! Thanks
oh and Merry Christmas!

Date "value" not working on INSERT Query

Hi,

The following INSERT query works in all aspects apart from the date value:

String InsertCmd =string.Format("INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},{1},{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text);

The value of txtPaymentDate.Text is "13/04/2006" but is inserted as a zero value (i.e. "01/01/1900").

In additon to replacing {1} with a string, I've tried changing {1} to both '{1}' and #{1}#, both of which are "caught" by my try/catch on the INSERT.

What am I doing wrong? Thanks very much.

Regards

Gary

That is because C# replace {1} with txtPaymentDate.Text, so the insert statement is changed to :

INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES (...,13/04/2006,...,...)

Where '...' stand for other texts. If you perform this command in Query Analyzer, you'll find 1900-01-01 is inserted instead of you expected value, as SQL needs a char value for datetime data type, not just the formated date without quotes. What we need to do is embed the date in a pair of quotes, so try to write your insertCmd as:

String InsertCmd =string.Format("SET DATEFORMAT dmy;INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},'{1}',{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text);

That works in my testing.

|||Google "parameterized sql query".|||

Thanks, Guys - you confirmed that I was on the right track.

'Found I had a combination of issues. First the issue the Iory Jay pointed out - and I had tried (putting the single quotes around the {1} parameter). Second a MM/dd/yyyy versus dd/MM/yyyy format problem.

I'm in New Zealand, which uses dd/MM/yyy format - and it's seems incredibly hard to solve these sorts of problems without resorting to brute-force programming - which I've had to do.

I guess, someday, I'll find the elegant way to convert from one format to the other - and in the mean time I'll continue to shake my head in wonder that software isn't reverse-engineered to make learning to use it easy - at least for straightforward things like referencing a control on a formview (FormView1.FindControl("controlname") is hardly quick and easy is it?), managing default values in DropDownLists (What a mission!), creating a link to another page (Response.Redirect? Come on!) and inserting a date into a database!

Regards

Gary

Date - 10 days

I working on a forecast report where I am pulling a date from a table and the
client wants to grab the date and subtract 10 days from the date.
I tried the expression =date.value-10, but this obviously didn't work.The expression is as follows:
=DateAdd("d",-10,Fields!Date.value)
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:27A2F586-962D-4F90-9029-699C4041C0C4@.microsoft.com...
>I working on a forecast report where I am pulling a date from a table and
>the
> client wants to grab the date and subtract 10 days from the date.
> I tried the expression =date.value-10, but this obviously didn't work.