Working with DATETIME Fields in SQL

I'm trying to delete data older than 24 hours from a particular table. Below is my SQL. It runs successfully but does not delete data. What is wrong with the syntax?


delete
from  IBLM$OPERATIONLOG FU
where FU.datetime = 'now' - (24/60)
0
Robin
7/24/2013 5:20:36 PM
embarcadero.interbase.general 923 articles. 0 followers. Follow

2 Replies
694 Views

Similar Articles

[PageSpeed] 42

Robin,

Your WHERE condition seems to only evaluate to true those records which contain FU.datetime equal to the expression result. It won't select records older than that.

Perhaps you can change it to...
 delete from  IBLM$OPERATIONLOG FU
  where FU.datetime < 'now' - (24/60)

OR...

 delete from  IBLM$OPERATIONLOG FU
  where FU.datetime < (CURRENT_TIMESTAMP - 1)  /* this will deduct 1 day from CURRENT_TIMESTAMP */

Best wishes,
Sriram

> {quote:title=Robin Tanner wrote:}{quote}
> I'm trying to delete data older than 24 hours from a particular table. Below is my SQL. It runs successfully but does not delete data. What is wrong with the syntax?
> 
> 
> delete
> from  IBLM$OPERATIONLOG FU
> where FU.datetime = 'now' - (24/60)
0
Sriram
7/24/2013 5:45:56 PM
Oh my gosh wow that's embarrassing. that's pretty bad that I missed that. Flying a million miles an hour, and this was the smaller of the problems I'm currently dealing with. :) Thanks Sriram. 



> {quote:title=Sriram Balasubramanian wrote:}{quote}
> Robin,
> 
> Your WHERE condition seems to only evaluate to true those records which contain FU.datetime equal to the expression result. It won't select records older than that.
> 
> Perhaps you can change it to...
>  delete from  IBLM$OPERATIONLOG FU
>   where FU.datetime < 'now' - (24/60)
> 
> OR...
> 
>  delete from  IBLM$OPERATIONLOG FU
>   where FU.datetime < (CURRENT_TIMESTAMP - 1)  /* this will deduct 1 day from CURRENT_TIMESTAMP */
> 
> Best wishes,
> Sriram
> 
> > {quote:title=Robin Tanner wrote:}{quote}
> > I'm trying to delete data older than 24 hours from a particular table. Below is my SQL. It runs successfully but does not delete data. What is wrong with the syntax?
> > 
> > 
> > delete
> > from  IBLM$OPERATIONLOG FU
> > where FU.datetime = 'now' - (24/60)
0
Robin
7/24/2013 5:53:43 PM
Reply:

Similar Artilces:

Compating DateTime field from sql table to DateTime Object
Hi. I have a SQL query that suppose to compare a given DateTime object to a datetime type coloumn : SELECT COUNT(*) FROM [QuestionnairesData] WHERE [SQClientUsername] = '" + Profile.UserName + "' AND [FillingDate] > " + Report.GetLastProductionReportDate(sqClient.SQCLIENTID)  the problem i get is on the last part of the select query. the DateTime object Report.GetLastProductionReportDate(sqClient.SQCLIENTID) gives me : 2/14/2007 15:49:04 PM and when i delete the time part it works well but i need to check for both date and time in this case. there is some issues with DateTime o...

DateTime unable to save in datetime field of SQL database
 Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up  Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar.  Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + descri...

How can I update an MS SQL 7.0 datetime field with embedded sql
I have a datetime field in MS Sql 7.0 named Revised_Date. I can update this date with my datawindow but I can't update it with embedded sql. I have tried the following which doesn't work UPDATE dbo.employees SET revised_date = date() ; produces invalid function error UPDATE dbo.employees SET revised_date = 2000-07-31 ; which yeilds 05/17/05 which is incorrect date ld_date time ldt_datetime ld_date = today ldt_date = datetime(ld_date) UPDATE dbo.employees SET revised_date = :ldt_date; which yields error How can I accomplish this ? T...

SQL datetime field
DateTime dt = Convert.ToDateTime(dtlabel.Text); string DT = dt.ToString("MM/dd/yyyy hh:mm:ss"); When I insert  string in database everything work fine but if string is empty I have this 1.1.1900 0:00:00 can I change this to Null or blank tnx.   instead of declaring the string like this string DT = dt.ToString("MM/dd/yyyy hh:mm:ss"); declare the string string DT = string.Empty; And befor assigning the value to string check for null value. if(dtlabel.Text!="") {DateTime dt = Convert.ToDateTime(dtlabel.Text); &nbs...

Working with Datetime fields
Hi Sorry, this is question for beginners. I'm using MS Visual Studio and MS SQL Server. I have the table 'RoutineActivities' and its field 'StartDate' whose DataType is Datetime. A tipical instantiation of this field would be '15/02/2004 10.32.53'. Now, in a query, I'm passing the parameter '@Date' with value '15/02/2004'. I would like to select records from 'RoutineActivities' Where StartDate=@Date. Obvioulsy no records are returned with this syntax. I'm not able to find a way to compare my '@Date' parameter with the Date part ONLY of the 'StartDate' field. Can an...

HOW DO I: Insert nulls into SQL dateTime field stored in SQL DB from a web UI textbox
I’m looking for feedback on the Best/Right way to Insert nulls into SQL dateTime field in SQL DB from a web UI textbox.    Option 1:     Presently implemented:  Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.Data.SqlTypes.SqlDateTime.Null, dtDateFollowUp.Text) Although ithis does what is needed it generates the following inner exception   ParamValue    {System.Data.SqlTypes.SqlDateTime} Object[System.Data.SqlTypes.SqlDateTime] {System.Data.SqlTypes.SqlDateTime}          ...

working with SQL 2005 interface but internally it works as SQL 2000
Hi! I have installed SQL 2000 and SQL 2005 in my computer.I tried to use some new features like row_number(),try..catch.. but are not working giving syntax error.” So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000: “Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)” So, how can I change it for working with SQL 2005?. It’s like I’m working with SQL 2005 interface but internally it works as SQL 2000. Can you ...

SQL Substring on DateTime field
Hi! Can anyone tell me how to perform a substring operation on a DateTime field. I'm using SQLServer. I have read that you may have to use casts but I have thus far been unable to get that to work. My statement: SELECT EventDate FROM tblEvent WHERE Category = 'GE'; I just want to cut the hours, minutes and seconds off, so that I am left with just the date element. Thanks all. Gren Try something like this:SELECT CAST(char(10), EventDate, 101) AS myShortDate FROM tblEvent WHERE Category = 'GE';For more information, see this Books Online topic: CAST and CONVERT.Terri MortonEngagem...

formatting a sql datetime field
Hi: I have a datetime field that I'm pulling from a sql db into a formView. So in the formView I have: <asp:TextBox ID="date_createdTextBox" runat="server" cssClass="formField" Text='<% # Bind("date_created") %>' />  If I want to just display the date, instead of the full date and time, is it better to convert it in the SQL query or in the formView itself? (and how would I convert it in the formview?)  You can use Text='<%# Bind("[date_created]","{0:d}") %>'  in the above def...

SQL DateTime Field Query
I was wondering if someone could help me here with some reporting I'm trying to do. For website visits, I currently use getdate() to have SQL insert the date and time of the visit in the table. I was wondering, is there a way to grab just the month from the field? I would like to chart this and I need to group the visits by month. I'm not sure how I would go about filtering just the month out of the entire date and time fields. You need DatePart function try the links below for details.  Hope this helps. http://www.sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk ht...

linking a sql field to point to the value of another sql field
is it possible to create hyperlinks in powerbuilder such that if you retrieve two fields from a database.. and want to display the first field on the screen and have it link to the value of the other field (when clicked) which is actually a URL address, you can? any help will be appreciated. thank you! ...

problem updating datetime field to SQL
HI I have a problem with this sql update: sql = "UPDATE Table1 SET remove=1, removaldate='" & DateTime.Now & "' " & _ " WHERE username='" & Session("username") & "'" comandosql = New SqlCommand(sql, conn) Try comandosql.Connection.Open() comandosql.ExecuteNonQuery() comandosql.Connection.Close() comandosql.Connection.Dispose() ........ The error is: System.Data.SqlClient.SqlException: The conversion...

Select all dates from SQL datetime field
I have a form that's filtering search results and porting to a gridview. The drop down is selecting from the date column of SQL, but i want it the default value to show all dates in the gridview. In a normal string field, you can just use "%", but a datetime field won't take this. What can i use to show all dates? Can you post the SQL you have currently and how the value from the ftont-end is passed to the DB?***********************Dinakar NethiLife is short. Enjoy it.*********************** <asp:SqlDataSource ID="LDSSqlDataSource1" runat="serve...

Update the timepart of datetime field in sql
I have a datetime field named EntryDate in one of the tables. I want to update the timepart of this field and provide a default time of 8:00 AM if there is no time portion attached to this date field. How can  i do this?    Hi, string textField = "10/9/2007"; // Your text field from form.textField = textField + " 8:00 AM";DateTime dt = DateTime.Parse(textField); Regards,Viktar KarpachMCTS .NET 2.0 Web Application, MCPD Web DeveloperASP.NET C# Technical Blog Thanks for replying   but I wanted the answer in t-sql TryDECLARE @DAT...

Web resources about - Working with DATETIME Fields in SQL - embarcadero.interbase.general

Is there a JQuery plugin to convert UTC datetimes to local user timezone?
If I have a tag: &lt;span class="utctime"&gt;2010-01-01 11:30 PM&lt;/span&gt; I would like a jquery script or plug in to convert every utctime ...


Targetprocess 3.7.3: Epic unit for Bug/Story cards, new DateTime functions in Webhook plugin
Webhook plugin supports new DateTime functions AddDays(DateTime date, int days) – Returns a new date with the number of days added. The number ...

Common Pitfalls when working with DateTime’s
In .NET, the DateTime structure provides us wonderful functionality, but this seemingly simple structure can cause a lot of headaches if you ...

Parsing Twitter Date format to .Net DateTime
Recently when I needed to parse the twitter datetime format into a .Net DateTime I found this post on the web showing how to do it. However, ...

C Sharp (programming language) - Wikipedia, the free encyclopedia
(pronounced see sharp ) is a multi-paradigm programming language encompassing strong typing , imperative , declarative , functional , generic ...

1753 - Wikipedia, the free encyclopedia
Language: English Afrikaans አማርኛ العربية Aragonés Asturianu Aymar aru Azərbaycanca বাংলা Bân-lâm-gú Basa Banyumasan Беларуская ‪Беларуская (тарашкевіца)‬ ...

Coded Smorgasbord: Schizophrenic Haiku Comments and More
... writes Malcom StandardResponse UnSubscribeNewsletterUserAccount( string opTinGUID,string email, string sellingRegion, string source, DateTime ...

Limburg.be - Welkom op de site van de provincie Limburg - Welkom_op_de_site_van_de_provincie_Limburg
visit wonen werken leven ontspannen infopunt home op trefwoord contactpersonen wegwijs sitemap print deze pagina Je bent hier: limburg.be Contact ...

Simples Ideias. Por Nando Vieira.
Simples Ideias Siga-me no Twitter Junho/2012 Usando o Vagrant como ambiente de desenvolvimento no Windows http://simplesideias.com.br/usando ...

Resources last updated: 12/10/2015 7:10:09 PM