Showing posts with label delete. Show all posts
Showing posts with label delete. Show all posts

Sunday, March 25, 2012

Date problem

Hi,
This code below in VB.net , delete a record in the database next day from inserting it to DB, for example:
If I add new record today 14-7-2005, and tommorow 15-7-2005 open to view the records available, i will not find it this record because it was deleted.

PrivateSub checkdate()

Dim ssqlAsString

Dim start_dateAsDate

Dim updcmdAs SqlClient.SqlCommand

mysqladap =New SqlClient.SqlDataAdapter(" select start_date from auction ", mySqlConn)

If Today.Date > start_dateThen

ssql = "delete auction where start_date < '" & Today.Date & "'"

updcmd =New SqlClient.SqlCommand(ssql, mySqlConn)

updcmd.ExecuteNonQuery()

EndIf

EndSub

BUTI want to change that code in a way that the record will be deleted after 3 days from the date it was inserted, that means the period for each record to stay in the DB is 3 days and after that will be deleted , is that possible ?? if yes how can it be changed??
Regards

I think you ought to insert a timestamp to record the datetime it was inserted (eg TSINSERT).
After this, you can delete all the records older than three days:
"DELETE FROM TABLE WHERE TSINSERT < " & Now.AddDays(-3).ToString
Another thing which you might do is to schedule a job on the database to automatically do the cleaning for you.
(but i have no experience in that so far)

Thursday, March 8, 2012

Date formats

I read this and got confused...

I use PHP and got this:
$sql = "delete from blabla where startdate< =
to_date('2000.05.05','YYYY-MM-DD')";
$result=odbc_exec($conn, $sql);

Result: Warning: odbc_exec() [function.odbc-exec]: SQL error:
[Microsoft][ODBC SQL Server Driver][SQL Server]'to_date' is not a
recognized function name.

to_date should work?

At the other end i'd like to read out startdate as a date var or format
it as I need... (dd.mm.yyyy). Convert makes me confused... help
please?

SHi

to_date is for Oracle

Pull out the date and do the conversion in php. Let the database do
data things

Sam

On Dec 4, 2:02 pm, "Sonnich" <sonnich.jen...@.elektrobit.comwrote:

Quote:

Originally Posted by

I read this and got confused...
>
I use PHP and got this:
$sql = "delete from blabla where startdate< =
to_date('2000.05.05','YYYY-MM-DD')";
$result=odbc_exec($conn, $sql);
>
Result: Warning: odbc_exec() [function.odbc-exec]: SQL error:
[Microsoft][ODBC SQL Server Driver][SQL Server]'to_date' is not a
recognized function name.
>
to_date should work?
>
At the other end i'd like to read out startdate as a date var or format
it as I need... (dd.mm.yyyy). Convert makes me confused... help
please?
>
S

|||

Quote:

Originally Posted by

I use PHP and got this:
$sql = "delete from blabla where startdate< =
to_date('2000.05.05','YYYY-MM-DD')";


Quote:

Originally Posted by

At the other end i'd like to read out startdate as a date var or format
it as I need... (dd.mm.yyyy). Convert makes me confused... help
please?


Checkout the "mssql.datetimeconvert" option in PHP.INI.

Regards,
lucm|||Sonnich wrote:

Quote:

Originally Posted by

I read this and got confused...
>
I use PHP and got this:
$sql = "delete from blabla where startdate< =
to_date('2000.05.05','YYYY-MM-DD')";
$result=odbc_exec($conn, $sql);
>
Result: Warning: odbc_exec() [function.odbc-exec]: SQL error:
[Microsoft][ODBC SQL Server Driver][SQL Server]'to_date' is not a
recognized function name.
>
to_date should work?
>
At the other end i'd like to read out startdate as a date var or format
it as I need... (dd.mm.yyyy). Convert makes me confused... help
please?
>
S


You should post at Oracle or Mysql forums

Madhivanan