Hi,
I have a report with datetime parameter which is required to be optional (there should be an option not to select this parameter which would cause in not narrowing result of this report by this parameter).
I used datetime parameter (to have the calendar control for selecting date) for which I enabled NULL value. The result is that next to this parameter there is a checkbox "NULL" which allows to send NULL as a value of this parameter. Everything is fine except for the prompt: "NULL"... Is there a way to change it? I would see there something like "Empty" or "Not selected", which would be understandable for users of the report.
Or is there any other way to send an empty date as a value of this parameter? (Setting some unusual value like 1/1/1900 for empty date is not sufficient as the empty value shouldn't appear in the parameter control).
I have a problem while trying to set "NULL" value to my datetime parameters.
I have two datetime parameters (AllowNull=True) on my report. (BeginDate and EndDate)
it works fine in preview mode in reporting design.
The problem occurs, if I try to set an emtpy value to these parameters in my asp.net application.
The code looks like;
Dim repParams(1) As ReportParameter
repParams(0) = New ReportParameter("BeginDate", String.Empty)
repParams(1) = New ReportParameter("EndDate", String.Empty)
-----
ReportParameter method requires String parameters. If user set any datetime value, I am sending "String.Empty" value to my paramaters in order to set them as NULL. But I am getting "rsReportParameterTypeMismatch Error."
How Can I set NULL value to these parameters from asp.net application?
I have a report with datetime parameter which is required to be optional (there should be an option not to select this parameter which would cause in not narrowing result of this report by this parameter).
I used datetime parameter
@daTo (to have the calendar control for selecting date) and check on "Allow Null" value. Here is default Value.
I've also tried to set DefaultValue expression was "=Nothing". Â
IN my query of data set, here is the part I use the parameter @daTo  ... Where (@daTo IS NULL OR TRUNC(@daTo) >= TRUNC(pe.start_time) ....
But when I run the report, I got below error:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
I think it @daTo value actually not null so it try to TRunc the value
I don't know how to set null value default in my report.
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.Â
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you  can not select null in multivalue parameter.How can i do it. i am using share point list.
Does anyone have a simple way of inserting a NULL value into sql 2000 datetime. I'm using vb.net. All I want is if the user does not enter a date in a textbox to to send a NULL value to the DB instead of having the db enter the default value as 1/1/1900. Thank you
I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand. The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting ‘’ but that also throws an exception (“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value�), so I don’t know how I can insert this value when the field is blank? Can anyone shed some light please? Thanks
I want to select records where AnswerDate is within 30 days of today, and include records that have a NULL AnswerDate. How can I tell DATEDIFF to treat NULL as Today? (I think I am looking for something like VBA's nz() function.)
SELECT RegistrationID , AnswerDate, (DATEDIFF(dd,AnswerDate,GETUTCDATE())) AS InvitationAge FROM Registration WHERE (DATEDIFF(dd,AnswerDate,GETUTCDATE())) <= 30
Ive created a Data Access Layer (xsd file) and some of the tables have DateTime columns. I need to allow NULL values to be saved, but when I try to do that I get an error In my code (c#) ive used Nullable DateTime, but when I try to assign a nullable Datetime to a DateTime column, I get this error cannot implicitly convert type 'System.DateTime?' to 'System.DateTime' I understand the reasons why, so how do I save null DateTime values to the underlying database through a schema ? In the schema file, the DateTime columns have <DBNULL> as the default value, AllowBDNull is true, but I am only allowed 'Throw exception' on the NULL value property I could probably do this another way (by not using a schema and writing the code myself) but Id prefer to keep the strongly typed code. I dont want to use MAx or MIn values for the default datetime values, so how can this be done ?
Hi I'm using DetailView and I have a text box which show the date. I have formated the date as shortDate {0:d} format. I want to insert/update null if that text box is empty. I have tried putting null value in my Update statement in sqlDataSource. And I'm getting error saying can't convert varchar to smalldatetime. If I don't set null value as above, some large date (01/01/2033) has been inserted in my database. Can anyone help me? Moe
I have defined fields in a table as datetime fields and allow nulls. SQL 6.5 is placing 1-Jan-1900 in all of these fields causing me to have to filter out these dates when retrieving a recordset. How do I get rid of this?
Hi. I wrote a sproc to insert values from a tab-delimited text file into SQL 2005. There is a column in the text file that contains datetime values. Some of the values may be null. I have defined my sproc as:[CODE]createproc [dbo].[spInsertTransactionData]@LoadTransactionCode tinyint,@AccountNumber char(19),@PostingDate char(8) = '01011900'asinsert into table1( LoadTransactionCode, AccountNumber, PostingDate)values(@LoadTransactionCode,@AccountNumber,cast(stuff(stuff(@PostingDate, 3, 0, '/'), 6, 0, '/') as datetime)) [/CODE]I am getting the error:"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."When I step into the sproc, I see that the problem arises when the code encounters a "blank" value for PostingDate.As always, thanks for the help!
Hi there ; This Problem is goin to make me crazy! I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 . I used asp.net 2.0 to insert datetime values, directly, using sth like DateTime.Now.ToString() . i use 2 selects as follows : 1)select * from X where Date1='8/9/2006 11:17:41 AM' 2)select * from X where Date2='8/9/2006 11:17:41 AM' #1 is OK, but the second one returns a row with all columns set to NULL . (X is the name of the table) Thanks in advance
Hi,I have a table adapter that I am using to call a stored procedure on a SQL server database to insert a record.Everything works ok, except I cannot figure out how to pass a null value to a DateTime field. In my SQL server database, the column allow nulls. In my typed dataset, the column is set to allow nulls.When I try to run the insert procedure with a null value: ListTableAdapter da = new ListTableAdapter(); da.InsertList(System.Data.SqlTypes.SqlDateTime.Null, Name); I get this error: Error 14 Argument '1': cannot convert from 'System.Data.SqlTypes.SqlDateTime' to 'System.DateTime?I've also tried passing it DBNull.Value, but I get an exception with that too.Can anyone steer me in the right direction with this?
If myDateTimeColumn contains a <NULL> value. How do you handle that when reading into a DateTime object in your code?DateTime myDate = Convert.ToDateTime(dr["myDateTimeColumn"]);Does not work, it throws: System.InvalidCastException: Object cannot be cast from DBNull to other types. I am curious as to what others are doing to handle this?
Hi, When I try to insert a new record into a table that has a datetime field that allows nulls, a default 01/01/1900 date is inserted instead of null. I recreated the table and set the datatype to smalldatetime and I still get the error. What have I missed?
In a VB.NET script, I am adding the dbnull value to a parameter that will populate a smalldatetime column: cmd.Parameters.Add("@unitHdApprove", System.DBNull.Value.ToString)
The stored procedure then defines the input as smalldatetime: @unitHdApprove smalldatetime,
However, the result is that the record is inserted with 1/1/1900 as the date value, instead of <NULL>.
I'm guessing that this occurs because the conversion of a '' to date will return 1/1/1900, and VB requries the parameter value to be a string (at least with this syntax), so System.DBNull.Value.ToString really equals ''.
I've rewritten the proc to accept the date as a string instead, and then for each date, cast it to a smalldatetime or set it to null as is appropriate. But this is a really bulky way to do things with more than a few dates! Is there any way to change what is in my VB code so that the procedure will insert the actual null value?
I have a flat file source and am bringing it into an OLE DB Destination connected to a SQL Server 2005 database. Since I am bringing in the data via a flat file, I have a conversion step in the data flow, which uses a "Data Conversion" transformation to convert each column to the appropriate destination data type.
When I run my SSIS package, if the datetime column in the flatfile is NULL, then the Data Conversion step fails. I have my Data COnversion step set to redirect error rows to an error output. So, I get all the rows with populated datetime values in the intended destination, and none of the rows with null datetime values (these go to the error destination.)
This is a problem! I need to make sure all valid rows fall through. It seems very odd to me that a NULL datetime could not be converted to a datetime data type. This makes no sense, since a datetime column can naturally hold NULL, just like any other type. So, my question is: is this a bug??? It seems to me it is. I can't believe this would be the desired behavior?!
Microsoft: please let us know whether this is expected behavior. If so, then what would the desired workaround be?
How to replace DateTime field with null value in SQL 2005 server I create a stored procedure aa, It works well, but sometimes I hope to replace CreateDate field with null value,I don't know how to doIt seems that datetime type is not null value create aa @CreateDate DatetimeAsUpdate cw set CreateDate=@CreateDate
I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:
SELECT * FROM ADMIN WHERE date_edited = NULL
I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:
There was a similar question to mine a few time ago about conditional execution, but I'm having some problems with it to which I couldn't find any answer, could anyone help?
My condition is checking if a DateTime from a DB table is null. I use the Execute SQL Task to retrieve the record (SELECT TOP 1 * FROM myTable WHERE conditions = TRUE), then I map 2 fields to their respective global variables.
Then I created the green arrow connector and used a precedence expression based on sucess. I used the expression IsNull(@variable). For some reason, even if the @variable comes as null - I checked the info in the DB - it still comes out as '1999-11-30'
I've looked around and the IsNull method can be used to also change the value in the event of it being null, could it be changing my variables value to this even if I didn't pass that value as a parameter to the function? If so, how else can I test it for null? I've tried "== null", "= null" and even ".equals(null)" - which I know is absurd, but I'm running out of options here...
Could you enlighten me, please??? Thanks in advance!
I have this question in one gridview: InsertCommand="INSERT INTO [Member] ([IC], [FirstName], [LastName], [Gender], [Birth], [Telephone], [Mobile], , [Address], [Postcode], [TimeOfRegistration]) VALUES (@IC, @FirstName, @LastName, @Gender, @Birth, @Telephone, @Mobile, @Email, @Address, @Postcode, @TimeOfRegistration)"If I want to insert into TimeOfRegistration with "System.DateTime.Today",can I have some expression to replace the "@TimeOfRegistration" in the SQL query? (<%# %>???Maybe?) If cannot, in the parameter control below, can I put it this way to achieve it? <asp:Parameter Name="TimeOfRegistration" Type=DateTime DefaultValue="<%# %>" />If so, how to put it? Thank you very much!
I have two tasks. 1. SQL Execution task to output a variable which must be a datetime value "2007/12/31 00:00:00.000" Variable name: Test Value Type: Datetime Value: 2007/12/31 00:00:00.000
2. Then in my OLEDB source i want use the parameter: I have setup the parameters for QRY = 0 (Test) QRY: WHERE WT.ApplyDtm = ?
I have two problems to this: 1. How do i know that my first task is bring back a value of datetime "2007/12/31 00:00:00.000" 2. In my QRY i have to put the parameter within '' , I tried '?' but it maons. The QRY should run: WHERE WT.ApplyDtm = '2007/12/31 00:00:00.000'
I'm importing data form an Excel file to a Sql Server Database. Some of the data imported represents time as a double type so i convert the times into DateTime to be inserted into the database. The time values that aren't available in the Excel file are 0.. so what i want to do is insert null into the database for all the values that are 0 in the excel file... How do i do that based on this code i have so far:protected void ButtonImport_Click(object sender, EventArgs e){PanelUpload.Visible = false;PanelView.Visible = false;PanelImport.Visible = true;LabelImport.Text = "";OleDbCommand objCommand = new OleDbCommand();objCommand = ExcelConnection(); OleDbDataReader reader;reader = objCommand.ExecuteReader(); while (reader.Read()){DateTime? in_1 = null;DateTime? out_1 = null;DateTime? in_2 = null;DateTime? out_2 = null; int emp_id = Convert.ToInt32(reader["emp_id"]);DateTime date_entry = Convert.ToDateTime(reader["date_entry"]);if (Convert.ToDouble(reader["in_1"]) != 0)in_1 = ConvertDoubleToDateTime((double)reader["in_1"]);if (Convert.ToDouble(reader["out_1"]) != 0)out_1 = ConvertDoubleToDateTime((double)reader["out_1"]);if (Convert.ToDouble(reader["in_2"]) != 0)in_2 = ConvertDoubleToDateTime((double)reader["in_2"]);if (Convert.ToDouble(reader["out_2"]) != 0)out_2 = ConvertDoubleToDateTime((double)reader["out_2"]); ImportIntoAttendance(emp_id, date_entry, in_1, out_1, in_2, out_2);} reader.Close();}protected void ImportIntoAttendance(int emp_id, DateTime date_entry, DateTime? in_1, DateTime? out_1, DateTime? in_2, DateTime? out_2){ SqlDataSource AttendanceDataSource = new SqlDataSource();AttendanceDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["SalariesConnectionString1"].ToString();AttendanceDataSource.InsertCommandType = SqlDataSourceCommandType.Text;AttendanceDataSource.InsertCommand = "INSERT INTO Attendance (emp_id, date_entry, in_1, out_1, in_2, out_2) " +"VALUES ('" + emp_id + "', '" + date_entry + "', '" + in_1 + "', '" + out_1 + "', " +"'" + in_2 + "', '" + out_2 + "')"; int rowsAffected = 0;try{rowsAffected = AttendanceDataSource.Insert();}catch(Exception ex){LabelImport.Text += "<font color=red>" + ex + "</font><br />";} }private DateTime ConvertDoubleToDateTime(double dbTime){string[] SplitTime = dbTime.ToString().Split('.');string hours = SplitTime[0];string minutes = String.Empty;string time = String.Empty; if (dbTime.ToString().IndexOf('.') != -1){if (SplitTime[1].Length >= 1){if (SplitTime[1].Length == 1)minutes = Convert.ToString(Convert.ToDouble(SplitTime[1]) * 10);else if (SplitTime[1].Length > 1)minutes = SplitTime[1];}}elseminutes = "00";time = hours + ":" + minutes;return Convert.ToDateTime(time);}
I have a table adapter for one of my SQL2005 tables, and in two different fields I accept a date time. Now 99% of the times, new rows to this table will be filled out using DateTIme.Now(), as a Time Stamp is what I'm going for.
Here is the line of code in question...cops_current_data_adapter.Insert(ProductOrder, Convert.ToInt16(Session["StationId"].ToString()), PartNumber, DateTime.Now, DateTime.Now, Convert.ToInt16(qty), 0); The second DateTime.Now is the one that can be null, and it's throwing a formatting error everytime I try and drop it in there. It's a FormatException, and there's not much more to the example except unhelpful tips like be careful when conveting a string to a dateTime, which I'm not doing. Needless to say for the code to compile, and then throw a Format error at runtime is a bit frustraiting. Any suggestions would be most appreciated
I have a sql proc. I need to make a parameter optional. I've setup the parameter to use a dataset as its source, and I need NOT to allow the value to be null. I've NOT selected allow nulls check box, But, when the parameter is null (‘’), still data comes back in a table as empty or if it is date will default to 1/1/1900. How do I send a null value to the database through sql proc and raise an error without inserting a null value? I’m hoping to avoid creating a special query just for that purpose.
I Created a Procedure that Generates an ID value based in the current Date (Year/Month) and a value which are stored in a table. This ID value is update in the table, and passed on to an output parameter as folows:
Code Snippet
CREATE PROCEDURE [dbo].[CNSPGetNewGuideID] @SectionID bigint ,@Year bigint ,@Month bigint ,@ID bigint output AS BEGIN
(...)
SELECT @ID = [Year]* 10000000 + [Month] * 100000 + [Number] FROM [dbo].[CNGuideID] WHERE [Year] = @Year AND [Month] = @Month
print 'New ID : ' print @ID END
This is working and the value printed here is the correct one.
but when I execute the procedure inside another one, the variable i pass on as an output parameter remains NULL...
Code Snippet
CREATE PROCEDURE [dbo].[CNSPInsertNewGuide] @SectionID int ,@ClientID int ,@UsuaryID bigint ,@UsuarySectionID int ,@ID int OUTPUT AS BEGIN
DECLARE @Date as datetime DECLARE @Year as int DECLARE @Month as int
SET @Date = GetDate() SET @Year = YEAR(@Date) SET @Month = MONTH(@Date)
I'm passing a list of values to a stored procedure using an XML parameter - using SQL 2005.
The stored procedure is used to search a table for matching values so this list can contain one or many values, or could be NULL to indicate all values should be returned.
Returning one or more than one value works fine in the code sample given below. How can I phrase my query to cope with the scenario of the parameter being NULL to select all values?
I currently have the following code to cope with one or many values being passed:
Code Snippet
SELECT * FROM dbo.MyTable WHERE idField IN (SELECT paramvalues.id.value('.', 'int') FROM @testxml.nodes('/rootnode/idfieldvalue') as paramvalues(id)) where @testxml is the XML parameter passed into the stored procedure.
