Datetime parameter / Report Viewer

I have a .net 2.0 web form with a report viewer control (RS 2005) and have hard coded the report server and path. The report has two date time parameters that have a default value set up. When the user clicks the calendar icon to change the date range, the page refreshes without displaying the calendar... I don't remeber having this problem in the past. The parameters function fine in Report Manager. Any idea what could be causing this?

I am having the same problem and ran across this thread. Hopefully it will help you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=591513&SiteID=1

DateTime param for SP causing BIG headache...!

I've got a stored procedure and one of the parameters is a DateTime. But no matter what I do to the string that's passed into the form for that field, it doesn't like the format. Here's my code:

SqlConnection conn =new SqlConnection(KPFData.getConnectionString());SqlCommand cmd =new SqlCommand("KPFSearchName", conn);cmd.CommandType = CommandType.StoredProcedure;SqlParameter param = cmd.Parameters.Add("@.DOB", SqlDbType.SmallDateTime);param.Direction = dir;param.Value = txtDOB.Text;// also have tried this:param.Value = Convert.ToDateTime(txtDOB.Text);// andparam.Value = Convert.ToDateTime(txtDOB.Text).ToShortDateString;

No matter what I do I always get a formatting error - either I can't convert the string to a DateTime, or the SqlParameter is in the incorrect format, or something along those lines. I've spent a couple hours on this and hoping someone can point out my obvious mistake here...??

Thanks for your help!!

eddie

The problem is as easy as you think: just pass string in correct format to the parameter The format here is the DATEFORMAT setting for current connection to SQL, which you can check by using:

dbcc useroptions

Looking at the 'dateformat' option, you'll see something like 'mdy', which means month-day-year, so '02/13/98' is a valid date while '13/02/98' is invalid (no 13th month). You can pass date in an absolute format of 'YYYY-MM-DD' (e.g. 1998-02-13), which can be recognized if itself is a valid date, no matter which dateformat the connection is using.

Another thing to remember about datetime in SQL is the date range. For more information, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.asp

|||What you had at first should have worked. What are they trying to type into the text field, and what culture is the asp.net thread running as?|||Thanks for the reply!

I'm still having problems, but I think it's an issue with the stored procedure since I'm no longer getting errors, just no results from the query... Is there a way to output the exact string sent to the database with the SP command and all of the parameters?

Thanks again,

eddie|||

Motley:

What you had at first should have worked. What are they trying to type into the text field, and what culture is the asp.net thread running as?

The culture is set as US, but I found out that I was entering the date incorrectly - the stored procedure expects it in this format: mm/dd/yyyy. I never tried using slashes... So I can reformat the text and submit it. Unfortunately I'm still getting jno results from the query, so I'm trying to figure out how to output the exact string sent to the database (see msg above) so I can run some tests directly on the db...

Thanks for your reply!

eddie|||Use the sqlprofiler tool.

Datetime datatype conversion to int hhmmyy format

Is there a way of converting a datetime data type in the form [DD/MM/YYYY HH:MM:SS] to an integer containing just the time in the form [HHMMSS].SELECT REPLACE((CONVERT(VARCHAR,GETDATE(),24)),':','')

This converts it to a varchar. You actually don't want to convert it to integer as it will drop off the leading zeros.

Datetime and conversion to smalldatetime.

I am placing DateTime into SQL using an ASP.NET form. The date should be formatted dd/mm/yyyy hh/mm/ss.

I am getting the error below. Is there any way to convert the format of the DateTime function from the ASP.NET end?

Thanks

mes

"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value"

DateTimes aren't "formatted". It sounds like you are giving it a string, that you think looks like a date. Give the database a real datetime (Or specify the sqlparameter as being of datetime type), and your problem should go away. If that string format isn't valid for your culture, well... You'll have to fix that separately, or manually convert your "DateTime in a string" to a format that your SQL Server wants.

Dates being saved incorrectly

Hi,
I've got a question about saving into datetime fields in a SQL Server table. A form I have create has two fields both for dates as well as other form fields, but the user may or may not fill in all the form fields, so when they click the save link I have a query which saves all the form fields whether they are blank or not. Unfortunately this is causing the two date fields to be saved in the database as "01/01/1900" even thoough they are blank fields.

What is a good way to not save blank date fields as "01/01/1900"?

Thanks

Stephen

Two choises:

1). If user doesn't leave the field blank, you can set DateTime = System.DateTime.Now, so that you can get the current dattime;

2). Allow your datetime column in your table as NULL value, or GetDate() as Default value, so that if user didn't put value, you can set the field as NULL or current datetime

Hope it helps

DatePicker on the report disappear when using FireFox

Please help me to solve this problem.

I put report viewer web control to web form and using remote mode in order to call my report.

In my report there are 2 datepickers for start date and end date.

When I click on on them, they disappear.

But I try to run this on Internet Explorer, it works. Date picker can display.

Help me

Somebody helps me please.

What should i do for this?

|||I have the same problem please help.|||I have the same problem please help.

Datediff formula on insert returning null value

I have a form with two date fields that the user will submit their requested vacation time off with. When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table. Everything inserts okay, but I am getting null for the request_duration. If I put dates in quotes and run the query it comes back with the right results. If I put the dates in the form and submit it, I get Null for the request_duration.

Thank you in advnace for any help on this!

INSERTrequest(emp_id,request_submit_date, request_start_date,request_end_date,request_duration,request_notes,time_off_id)Select@.emp_id,GETDATE(),@.request_start_date,@.request_end_date, 1 +DATEDIFF(day, @.request_start_date, @.request_end_date) - (selectcount(*)from WeekEndsAndHolidayswhere DayOfWeekDatebetween @.request_start_dateand @.request_end_date),@.request_notes,@.time_off_id

Either request_start_date or request_end_date is coming across as null. Are you sure that request_duration is the only null field?|||

Motley,

Thanks for the response. It took a while to get posted and I figured it out way before it got posted. I had worked on it for about an hour before I posted this and figure it out 5 minutes after I posted it. I didn't have one of my text boxes bound correctly.

Date/Time Stamp

When a record is written to a table (via a asp form), I'd like the time
and date from the server to automatically populate a column in that
table. From what I can tell, timestamp isn't working. I rather not
have the time come from the client.

Thanks for the help.Add a column with a default of CURRENT_TIMESTAMP. This is nothing to do
with TIMESTAMP, which is the SQL Server keyword for a row-versioning
column, not for date and time.

ALTER TABLE your_table ADD date_created DATETIME NOT NULL
CONSTRAINT df_your_table_date_created DEFAULT CURRENT_TIMESTAMP

--
David Portas
SQL Server MVP
--|||alternatively, you can also use as

ALTER TABLE your_table ADD date_created DATETIME NOT NULL
CONSTRAINT df_your_table_date_created DEFAULT getdate()

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***