Updating datetime field sql

Here is the structure of one of the table I need to fix.

Original Dim category Table As New Data Table adapter.

I am using PHP and SQL through an ODBC connection to update a Date Time field that had a date in it to NULL. I have tried setting it NULL with and without quotes but still no success.

I know the SQL works because if I input a date instead of NULL it works. $dateapproved = "NULL"; $updatedateapprovedsql = "UPDATE [Problem Data Table] SET [Corrective action approved]='$dateapproved' WHERE [8D Number]='$pionumber'"; $updatedateapprovedresult=odbc_exec($connection,$updatedateapprovedsql); In codeigniter it seems to accept $update Data['expires_on'] = null without throwing an error and it works like charm.

As you can see, the date part is the default value for a Date Time field and is the part I need to fix.

I have the following select, which gives me the rows I need to fix. 1900-01-01 is the "zero" date, so if you add it to something else, you get that same value. If there are cases where Date Time Field1 has the correct date but Date Time Field2 doesn't, you might want to do this as two separate queries. Date Time Field1)), Date Time Field2 = convert(datetime,convert(int,convert(float,t. Date Time Field2)), FROM Table1 t WHERE (Date Time Field1 UPDATE tab SET Date Time Field1 = ltrim(str(datepart(year, Date Time Field3))) '-' ltrim(str(datepart(month, Date Time Field3))) '-' ltrim(str(datepart(day, Date Time Field3))) ' ' ltrim(str(datepart(hour, Date Time Field1))) ':' ltrim(str(datepart(minute, Date Time Field1))) ':' ltrim(str(datepart(second, Date Time Field1))) '.' ltrim(str(datepart(millisecond, Date Time Field1))) , Date Time Field2 = ltrim(str(datepart(year, Date Time Field3))) '-' ltrim(str(datepart(month, Date Time Field3))) '-' ltrim(str(datepart(day, Date Time Field3))) ' ' ltrim(str(datepart(hour, Date Time Field2))) ':' ltrim(str(datepart(minute, Date Time Field2))) ':' ltrim(str(datepart(second, Date Time Field2))) '.' ltrim(str(datepart(millisecond, Date Time Field2))) WHERE (Date Time Field1 set Date Time Field1 = case when cast('' as datetime) = cast(floor(cast(Date Time Field1 as float)) as datetime) then Date Time Field1 Date Time Field3 else date1 end, Date Time Field2 = case when cast('' as datetime) = cast(floor(cast(Date Time Field2 as float)) as datetime) then Date Time Field2 Date Time Field3 else date2 end where Date Time Field1 This works by checking to see if the date and time, converted to a float and then floored (which removes the time part) equals 1st Jan 1900.

576

Leave a Reply