Handling A NULL Datetime In DATEDIFF()

Jan 28, 2008

 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

 

Thanks

View 3 Replies


ADVERTISEMENT

Handling A Null Datetime Column

Nov 12, 2007

can anybody tell me how to do a select query on a datetime field where if i have a null value in that column, i need to display a some character.

View 4 Replies View Related

DateTime Using DateDiff

Dec 8, 2004

Hello everyone.

Im currently using the DateDiff function to filter my DateTime columns but am finding it somewhat troublesome. Currently I am having to write the same select statement 3 times if I want to filter by month, year or all (ignoring dates).

To find @PurchaseTotal for the year, I have to write the following:


SELECT
@PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
DateDiff(yyyy, DateOf, @IntervalDate) = @Interval



To find @PurchaseTotal for a month, I have to write the following:


SELECT
@PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
DateDiff(mm, DateOf, @IntervalDate) = @Interval



To find @PurchaseTotal for all the records, I have to write the following:


SELECT
@PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses


I've tried the following code but I get an error.

DateDiff(@DateParameter, DateOf, @IntervalDate) = @Interval

Error says something like "incorrect parameter 1 for DateDiff."

It seems you have to write a different select statement for month, day and year. Also If you want a total from all the records you have to write yet another select statement.

Does anyone know of a DateTime function that allows parameters to specify for month, day and Year? Also does anyone know of a DateTime function that works like the COALESCE function to where you can send it a NULL value and give you all the records?

Thank you ahead for any direction you can give.


Alec

View 1 Replies View Related

Datediff Formula On Insert Returning Null Value

Sep 28, 2006

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) - (select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @request_start_date and @request_end_date),
@request_notes,
@time_off_id   

View 2 Replies View Related

Handling Null Dates

Feb 8, 2008

Hi, I have a database field for completion dates - until a task is completed, there is no date and at the moment I have null values in this field.My problem arises when searching the records.  I have a search form which passes parameters via a query string to a SqlDataSource.  The SqlDataSource has the CancelSelectOnNullParameter set to true, so if any fields on the search form are left blank, they are ignored.  For other dates, my sql query contains something like  (Job.EnteredAt < ISNULL(DATEADD(d, 1, @EntTo), '2099-01-01')) This approach doesn't return any records for the completion date as there is no date to compare to the '2099-01-01'.Can anyone give me any tips on how I should handle this?  I'm willing to change my structure, search page or sql query!Thanks, Neil 

View 1 Replies View Related

Handling NULL Values.

Oct 19, 2007

Hi,

I would like to know the solution for the below problem.

Actually I have a single Hibernate xml file which has the query on it. And I am accessing the xml file for SQL server 2005 and Oracle 10G. But the query is working in Oracle and not working in SQl Server.

I noticed from the query that handling 'NULL' is the problem.

Findings:
1. The following query is the same in both Oracle and SQL Server, which is causing the Issue.
SELECT *
FROM CLIENT_SKU AS o
WHERE o.OU_ID = 20000000
AND ((o.CLIENT_SKU_START_CODE <= 'wwwww' AND o.CLIENT_SKU_END_CODE >= 'wwwww')
OR (o.CLIENT_SKU_START_CODE <= '' AND o.CLIENT_SKU_END_CODE >= '')
OR (o.CLIENT_SKU_START_CODE >= 'wwwww' AND o.CLIENT_SKU_END_CODE < '')
OR (o.CLIENT_SKU_START_CODE = 'wwwww' AND o.CLIENT_SKU_END_CODE is null))

CAUSE:
Oracle treats NULL and the empty string almost the same making the latter almost an alias for NULL
The following two statements produce identical results:
1. update employee set lastname = ''
2. update employee set lastname = NULL
In our case, the CLIENT_SKU_END_CODE is an empty string which is being checked with an empty string returns False in Oracle where as returns True in Sql Server causing the issue.

It would be great if any one who can give some possible solution to resolve the same.

Thnx

View 3 Replies View Related

Handling Null Date Values

Jun 30, 2000

I would like some suggestions on how to keep from displaying the value "1/1/1900" on my asp pages when my recordset field returns a Sql date value that is null

Thanks for any tips
Jim

View 1 Replies View Related

Null Value Handling In SSIS Package

May 24, 2007



Hi All



I am facing this problem while loading data from text file into Table.

Scenario is -



There are chances of having spaces for null values in text file.



when i m trying to rum my SSIS package this is getting failed.



How can i avoid this problem? i want null values to be inserted if ther is spaces for that field in text file.



Thanks,

Anshu

View 1 Replies View Related

Handling Null Fields With Three-tier Architecture

Nov 28, 2006

I using Visual Web Designer Express (with Visual Basic), with a SQL Server 2000 database.   I have a prototype application running satisfactorily using code that builds queries, but I'm now trying to rebuild the application "properly" using three-tier architecture.  I have been following the principles of Scott Mitchell's tutorials.  I have created an database .xsd with a table adaptor, and replaced the rather grotty query-building code in the business layer with better code referencing the table adaptor.   Thus where the first version had code: -
        Dim queryString As String = "SELECT * FROM NZGDB_User WHERE USRid = '" & Userid & "'"        Dim message As String = ""        Dim Found As Boolean = False        Try            Using connection As New SqlConnection(GDB_AppSettings.Connection)                Dim command As New SqlCommand(queryString, connection)                connection.Open()
                Dim reader As SqlDataReader = command.ExecuteReader()
                If reader.Read() Then                    Found = True                    _USRid = reader("USRid")                    _USRPassword = reader("USRPassword")                    _USREmail = reader("USREmail")                    _USRTitle = reader("USRTitle")                    _USRName = reader("USRName")                    _USRRole = reader("USRRole")                    If IsDBNull(reader("USRAgreedConditions")) = False Then                        _USRAgreedConditions = reader("USRAgreedConditions")                    End If                End If                reader.Close()            End Using        Catch ex As Exception            If Left(Err.Description, 68) = "An error has occurred while establishing a connection to the server." Then                Return "Cannot open database to logon"            Else                Return Err.Description            End If        End Try
the new version is much more elegant: -
        Dim taUser As New NZGDBTableAdapters.NZGDB_UserTableAdapter()
        Dim tbUser As NZGDB.NZGDB_UserDataTable = taUser.GetUserByUserid(userid)        If tbUser.Count <> 1 Then   '   Anything other than 0 or 1 should be impossible            Return "User not found"        End If
        Dim trUser As NZGDB.NZGDB_UserRow = tbUser(0)        _USRid = userid        _USRPassword = password        _USREmail = trUser.USREmail        _USRTitle = trUser.USRTitle        _USRName = trUser.USRName        _USRRole = trUser.USRRole        _USRAgreedConditions = trUser.USRAgreedConditions
However, there is a problem.  The database field USRAgreedConditions is a Datetime field that can be null.  The new version works perfectly when it is NOT null, but throws an exception: -
System.Data.StrongTypingException was unhandled by user code  Message="The value for column 'USRAgreedConditions' in table 'NZGDB_User' is DBNull."  Source="App_Code.wt2vzoc1"  ....
There is no point in writing: -        If Not IsDBNull(trUser.USRAgreedConditions) Then            _USRAgreedConditions = trUser.USRAgreedConditions        End Ifbecause the exception occurs within the automatically-created code in the data access layer.  I tried changing the Nullvalue property of the field USRAgreedConditions in the table adaptor, but the only valid option is (Throw Exception) unless the field is a String.  Of course USRAgreedConditions is a Datetime field, so I can't change the property.
It seems that my only options are: -1.   To stick with the old query-building code.   But this doesn't give me the advantages of a proper 3-tier architecture2.   To change the generated code in wt2vzoc.  This MUST be a bad idea - surely I should leave this code untouched.  Besides, what if the table adaptor has to be regenerated when I change the table design?3.   Code a Try block within the business layer: -    Try         _USRAgreedConditions = trUser.USRAgreedConditions    Catch ex As Exception         _USRAgreedConditions = Nothing    End Try
This seems to work OK, but seems less elegant than the original code in the old version: -       If IsDBNull(reader("USRAgreedConditions")) = False Then            _USRAgreedConditions = reader("USRAgreedConditions")       End IfIs there a better way?

View 4 Replies View Related

Insert NULL Value Into DateTime

Dec 11, 2006

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

View 3 Replies View Related

DateTime Parameter With NULL Value

Apr 8, 2008

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).

Thank you,
Michal

View 9 Replies View Related

INSERT NULL Value + DateTime

Dec 10, 2007

Hi,

If a datetime variable is empty I want to insert null into the database table.

How do i do this? Below code doesn't work. Get this error...
Cannot implicitly convert type 'System.DBNull' to 'System.DateTime'




Code Block
if (myDataGridView.Rows[iRow].Cells["myDateDataGridViewTextBoxColumn"].Value.ToString() != "")
{
myDateReport = DateTime.Parse(myDataGridView.Rows[iRow].Cells["myDateDataGridViewTextBoxColumn"].Value.ToString());
}
else
{
myDateReport = DBNull.Value;
}

View 2 Replies View Related

Set NULL To Datetime Parameter

Dec 14, 2006

Hi All,

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?

Thans alot in advance.

View 9 Replies View Related

Inserting NULL Into Datetime Field

Aug 17, 2007

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
 

View 2 Replies View Related

Xsd Files And Null Datetime Columns

Feb 5, 2008

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 ?

View 3 Replies View Related

DateTime Null In Sql Server Database

May 11, 2005

Hi,
I'm using this source code in order to set the DateTime field of my Sql Server database to null.I am retreiving dates from an excel sheet. If no date is found, then I set my variable myDate to DateTime.MinValue then i test it just before feeding my database.
I have an error saying that 'object' does not contain definition for 'Value'.
In french : Message d'erreur du compilateur: CS0117: 'object' ne contient pas de définition pour 'Value'dbCommand.Parameters["@DateRDV"].Value = System.Data.SqlTypes.SqlDateTime.Null;
The funny thing is that in the class browser i can see the Value property for the class Object...
C#, asp.netstring  sqlStmt ; string  conString ; SqlConnection cn =null; SqlCommand cmd =null; SqlDateTime sqldatenull ; try{sqlStmt = "insert into Emp (Date) Values (@Date)  ";conString = "server=localhost;database=Northwind;uid=sa;pwd=;";cn = new SqlConnection(conString);cmd = new SqlCommand(sqlStmt, cn); cmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));sqldatenull = System.Data.SqlTypes.SqlDateTime.Null; if (myDate == DateTime.MinValue) {cmd.Parameters ["@Date"].Value =sqldatenull ;}  else{cmd.Parameters["@Date"].Value = myDate;}cn.Open();cmd.ExecuteNonQuery();Label1.Text = "Record Inserted Succesfully";}catch (Exception ex){Label1.Text = ex.Message;}finally{cn.Close();}

View 3 Replies View Related

Cant Insert NULL To DateTime Field

Jun 5, 2006

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

View 5 Replies View Related

Null Datetime Field Contains 1-Jan-1900

Feb 10, 1999

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?


thanks!

View 1 Replies View Related

Null Datetime Value Problem In Sproc

Aug 28, 2006

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!

View 3 Replies View Related

Sql SELECT Returning NULL On DateTime Field

Aug 8, 2006

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

View 6 Replies View Related

How To Install A Null Value Into A DateTime Column Via A TableAdapter?

Feb 8, 2007

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?

View 11 Replies View Related

How Do You Handle Null DateTime Values From SQL Server?

Aug 17, 2005

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?

View 6 Replies View Related

Null Values In Datetime Fields, Howto?

Jul 12, 1999

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?

View 1 Replies View Related

Sending Null DateTime Value To Stored Procedure From VB

Nov 3, 2005

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?

Thanks,
Sarah

View 2 Replies View Related

Data Conversion - NULL Datetime Causes Failure

Jul 9, 2007

Hi,



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?



Thanks

View 5 Replies View Related

How To Replace DateTime Field With Null Value In SQL 2005 Server

Apr 7, 2006

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
 
 

View 3 Replies View Related

Mssql Won't Find NULL Values In Datetime Field??

Feb 13, 2007

Hi

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:

thanks

View 10 Replies View Related

Reporting Services :: DateTime Parameter With NULL Value Is Default

Jul 4, 2015

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.

View 4 Replies View Related

Conditional Execution With Null Checking For DateTime Field

Mar 11, 2008

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!

View 4 Replies View Related

Howto Insert A Null DateTime Into A Sql Server 2005 Database

Apr 2, 2008

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);}

View 3 Replies View Related

DateTime.Now, An Allowed Null Field, And My Insert Function From My Dataset

Jun 6, 2008

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

View 1 Replies View Related

Seeking Correct Syntax To Pass A NULL Value For A Datetime Field From An Asp Page To SQL

Jan 13, 2006

Hello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!

View 7 Replies View Related

Problem With Isnull. Need To Substitute Null If A Var Is Null And Compare It To Null And Return True

Sep 20, 2006

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you

set ansi_nulls off


go

declare

@inFileName VARCHAR (100),

@inFileSize INT,

@Id int,

@inlanguageid INT,

@inFileVersion VARCHAR (100),

@ExeState int

set @inFileName = 'A0006337.EXE'

set @inFileSize = 28796

set @Id= 1

set @inlanguageid =null

set @inFileVersion =NULL

set @ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @inFileName

AND DR.FileSize =@inFileSize

AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@inlanguageid,null)

AND DR.[ID]= @ID

)

go

set ansi_nulls on

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved