Current Date/time Formula
Jul 23, 2005
Hi,
I would like to have a date_last_modified field for one of my SQL
tables. Instead of coding my front end to keep up with this field is
there a way to user a formula for this column in Enterprise Manager so
that each time a record is created or updated the current date/time
will be inserted/updated? I tried using getdate() but then the field
always has the current time, which is not what I am looking for. I am
looking for the time the row is updated or created. Is this possible?
Thank you!
View 2 Replies
ADVERTISEMENT
Jun 8, 2008
I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77
I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
lack of idea to put the TIME condition.
Plz help me..
View 14 Replies
View Related
Dec 29, 2005
I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett
View 7 Replies
View Related
Mar 16, 2015
I am new to SQL and want to understand how to update current date and time into DB2 in SQL.
View 1 Replies
View Related
Jul 23, 2007
Riding MVJ's excellent F_TABLE_DATE function, I often use this view for current date and time information.SELECTCURRENT_TIMESTAMP AS Now,
DATEADD(MINUTE, DATEDIFF(MINUTE, 0.00069445, CURRENT_TIMESTAMP), 0) AS previousMinute,
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CURRENT_TIMESTAMP), 0) AS thisMinute,
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CURRENT_TIMESTAMP), 0.00069445) AS nextMinute,
DATEADD(HOUR, DATEDIFF(HOUR, 0.0416667, CURRENT_TIMESTAMP), 0) AS previousHour,
DATEADD(HOUR, DATEDIFF(HOUR, 0, CURRENT_TIMESTAMP), 0) AS thisHour,
DATEADD(HOUR, DATEDIFF(HOUR, 0, CURRENT_TIMESTAMP), 0.0416667) AS nextHour,
DATEADD(DAY, DATEDIFF(DAY, 1, CURRENT_TIMESTAMP), 0) AS previousDay,
DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0) AS thisDay,
DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 1) AS nextDay,
DATEADD(WEEK, DATEDIFF(WEEK, 7, CURRENT_TIMESTAMP), 0) AS previousWeek,
DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0) AS thisWeek,
DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 7) AS nextWeek,
DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0) AS previousMonth,
DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AS thisMonth,
DATEADD(MONTH, DATEDIFF(MONTH, -1, CURRENT_TIMESTAMP), 0) AS nextMonth,
DATEADD(QUARTER, DATEDIFF(QUARTER, 92, CURRENT_TIMESTAMP), 0) AS previousQuarter,
DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CURRENT_TIMESTAMP), 0) AS thisQuarter,
DATEADD(QUARTER, DATEDIFF(QUARTER, -1, CURRENT_TIMESTAMP), 0) AS nextQuarter,
DATEADD(YEAR, DATEDIFF(YEAR, 365, CURRENT_TIMESTAMP), 0) AS previousYear,
DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0) AS thisYear,
DATEADD(YEAR, DATEDIFF(YEAR, -1, CURRENT_TIMESTAMP), 0) AS nextYearFor example, If I want this weeks data I just join against this view and useWHEREDtCol >= thisWeek
AND DtCol < nextWeekOr previous months dataWHEREDtCol >= previousMonth
AND DtCol < thisMonth
EDIT: Faster week calculations.
View 5 Replies
View Related
Jul 23, 2005
Hi all, I have a question about inserting records into sql server. Iam brand new to sql server, always using oracle previously. Beforetoday, I had always written statement such as this:INSERT INTO TABLE (COL1) VALUES SYSDATE;How is this accomplished in sql? I am using a datetime data type, Ihope that is correct . . .Thank you for your help.Ryanp.s. I tried getdate(), getdate, sysdate, and current_timestamp. Allto no avail :(
View 5 Replies
View Related
Oct 22, 2007
I want to capture date and time record is inserted to database.
My table got a field called
field name datatype length
rec_datetime datetime 8
what is the best way to enter value?.
View 7 Replies
View Related
Aug 13, 2007
HELP... I’m new to ASP.NET so please
excuse my inexperienced question. I’m using VWD 2005 and a remote SQL 2000.In the (Configure Data Source) under the (Select) tab I have
this simple query:
Select * from calendarWhere event_date = now
Right off the that bat you can probably see the problem. I
just want to retrieve all event at is equal to today (current date). In classic
ASP is can use <%now%> and in ColdFusion I can use #now()#. What is the
proper syntax the get the server current date and time, like (getDate()) in SQL?
Thank you in advanced for you help!
View 6 Replies
View Related
Nov 2, 2006
How can I set a column in a table to auto update the date and time everytime something in that row is updated or when the row is first added? Thanks ahead for the help,Jason
View 4 Replies
View Related
Mar 30, 2007
I need an SQL string that inserts the current date into a database.
So far I have tried:
SQL = "INSERT INTO X (START_DATE) VALUES ('" & Date.Now & "')"
mycomm = New SqlCommand(sql, myconn)
mycomm.ExecuteNonQuery()
However, there is a problem with the SQL command. The problem is related to the date. Is there a way of programatically inserting the current date/time into the SQL database? Language used is VB.
View 1 Replies
View Related
Jun 2, 2008
Greetings,I have an e-timesheet application.i made a function that takes the startdate of the week:public SqlDataReader GetProjectsActive(DateTime SheetDate) { SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlCommand myCommand = new SqlCommand("GetProjectsActive", myConnection); myCommand.CommandType = CommandType.StoredProcedure; SqlParameter parameterSheetDate = new SqlParameter("@SheetDate", SqlDbType.DateTime); parameterSheetDate.Value = SheetDate; myCommand.Parameters.Add(parameterSheetDate); myConnection.Open(); SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection); return dr; } i run the application and i pass the value 6/1/2008 (date without time) to the GetProjectsActive but what i notice that the storedprocedure adds the current time to the passsed date when it has been executed so some projects will not appear since the startdate and enddate of the project are only dates so i try this solution: parameterSheetDate.Value = SheetDate.Date;and it works finebut my question why the current time has been added to the storedprocedure automatically although i pass only the date 6/1/2008 date????your help is highly appreciated.Best regards.
View 6 Replies
View Related
Dec 26, 2013
I come in to work 6.30am, and need to audit what happened from 5pm when I left to 6.30am this morning. I have used code to search 13.5 hours back from any given time:
SELECT * FROM TRANSACTION_HISTORY
WHERE TRANSACTION_HISTORY.ACTIVITY_DATE_TIME > (SELECT DATEADD(hour,-13.5,(SELECT MAX (TRANSACTION_HISTORY.ACTIVITY_DATE_TIME) FROM TRANSACTION_HISTORY)))
Problem is if I run query later, I lose time from the start, eg. If I run query at 7am, I only get results from 5.30pm onwards. Rather than change criteria every day, I wanted to able to search from 6.30am of the current day, back to 5.30pm of the previous day.
View 3 Replies
View Related
Apr 25, 2008
Hi,
I'm making some sort of application where people can add their resume.
They also need something to add places where they have worked or currently are working.
I have a form where they can add this and i add this experience using the following stored procedure:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_Insert_Experience]
@ExperienceId uniqueidentifier,
@ExperienceEmployee nvarchar(100),
@ExperienceFrom datetime,
@ExperienceUntil datetime,
@ExperienceQualifications nvarchar(250),
@ExperienceTechnologies nvarchar(250),
@ExperienceTasks nvarchar(250)
as
insert into Experiences
values(@ExperienceId,@ExperienceEmployee,@ExperienceFrom,@ExperienceUntil,@ExperienceQualifications,
@ExperienceTechnologies,@ExperienceTasks);
It must be possible to add the place where they currently are working. Then the ExperienceUntil has to be something like still going on. Then I decided that the user then had to set the current date.
But what I want is the following, I want that the ExperienceUntil keeps updating automatically, like everytime i get that record, it has to have current date.
Is this possible ?
But if the ExperienceUntil isn't the current date , it just had to take the supplied parameter date.
View 7 Replies
View Related
Apr 24, 2001
Does any of you have an SQL example that will convert a given date to the number of seconds since 1970? We have one that does the opposite (seconds to date), but can't figure out how to go date to seconds.
View 1 Replies
View Related
Dec 21, 2005
Manish writes "Hi there, any help will be greatly appreciated as i am struggling with this problem :)
I have a field in a database called time which also has the value time in it.
What i want to do is to have a sql query which will display all the records in that table where 5 mins have gone after that time."
View 4 Replies
View Related
Jun 30, 2007
This is driving me nuts..
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. Any pointers would be helpful.
Thanks
View 3 Replies
View Related
Sep 22, 2006
I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.
View 4 Replies
View Related
Sep 29, 2015
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
View 3 Replies
View Related
Mar 10, 2008
I have a reference table that currently has no web front-end. It's a small table(<10 rows) that's not going to change very often (maybe once every few months).
We manually update rows on the table via the GUI table interface in Enterprise Mgr., not in T-SQL.
What I'd like to do is have SQL Server automatically update the "Last_Modified" column with the current timestamp. I can do it on an Insert using the GetDate() function, but if I update a row, this doesn't work.
Is there a function I can use that can auto-populate for both insert and updates?
View 4 Replies
View Related
Nov 21, 2007
Hi,
I tried to create parameters in my report. I need one date picker. So i select data type as datetime. in default values i selected as non-queried and in date time functions i selected =Today. But am unable to set current date. Please help me to solve it.
Regards
Nataraj.C
View 2 Replies
View Related
Aug 5, 2015
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
View 5 Replies
View Related
Apr 22, 2007
I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.
So I have a question
1.) How do I get today's date in C# and how should this be passed to SQL server?
View 7 Replies
View Related
Jul 23, 2014
I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.
=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,
[Code] ....
Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.
SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID
View 9 Replies
View Related
Oct 21, 2013
the requirements are to return person ID with the most recent leaving date from a scheduled dept, who has previously arrived at an unscheduled dept within 7 days, but just now my query shows all the previous leaving dates within 7 days of that unscheduled dept arrival, I only want the last leaving date before the arrival at the unscheduled dept:
So for instance looking at a copy of one person's date below I have:
PersonID Last Dept Arrival Date To Last Dept Leaving Date From Last Dept Next Arrival Date to Unscheduled Dept
======== ================= ========================= =========================== =====================================
0106573268Dept 5 2013-03-01 2013-03-03 2013-03-05
0106573268Dept 6 2013-02-27 2013-02-27 2013-03-05
0106573268dept 2 2013-02-26 2013-02-26 2013-03-05
In the data above I only want to return the first row, which is the most recent leaving date before arrival at an unscheduled dept.
My query is much the same as before except my inline view is looking at the data for last scheduled leaves from depts in my inline view and also the outer query returning all arrivals to the unscheduled dept:
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept"
,b.leaving_Date AS "leaving Date From Last dept",
a.[arrival Date] as "Next arrival Date to AREA_GH"
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
FROM scheduled_Leaves
where [leaving date] is not null) b
ON a.Person_ID = b.Person_ID
[code]....
View 11 Replies
View Related
Nov 23, 2015
I have a the following date variables that's being set around the current date. how to adjust it to work around a date variable @Date instead of the current date? Lets the @Date = 2015-06-30 then the it would adjust the variable below accordingly. I'm assuming the getdate()) needs to be replaced with @Date but I can't seem to get it to work.
Declare @EndOfLMPriorYear Date = DateAdd(yyyy,-1,EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0)))
Declare @EndOfPriorMonth Date = EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-2,0))
DECLARE @EndOfLastMonth Date = EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0))
View 3 Replies
View Related
Sep 1, 2015
In SQL reporting, How do I add the formula in the Formula bar?All the data is coming from a sproc.
View 3 Replies
View Related
Jul 20, 2005
Hiis there a "SQL string" to obtain date and time of a SQLServer machine ?thank u
View 1 Replies
View Related
Oct 25, 2007
how is it possible to set a filed in a database to automatically add the current time to the filed at run time, is this possible; this filed has already been identified as 'DateTime' .
View 4 Replies
View Related
Sep 3, 2014
if now the system time is 2014-12-23 23:45:345 . then I want to calculate the sales amount with total and get current date and current hour filter by hh:00-hh:59 , my desire output is like below :
Current time:23:50 run this query
Date Time amount
2014-12-23 23:40 $10
2014-12-23 23:01 $5
2014-12-23 23:39 $5
Total :$20
View 1 Replies
View Related
Jul 20, 2005
Hi,I am having a problem with aquery. Firstly here is a script to createthe table and insert some sample data:CREATE TABLE [dbo].[tbltemp999] ([Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[EventDate] [datetime] NOT NULL ,[EventID] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6005)The EventIDs 6005s represents a system start up and an EventID 6006represents a system shut down/restart. I already have a query tocalculate the downtime of a particular date range. So when i want tofind the total uptime of a date range in the past i do the following:Date1 00:00:00.000 to Date2 23:59:59.999 then subtract the downtime.My problem arises when Date2 is todays date as the time of23:59:59.999 would not have reached so the calculations are alwayswrong.How can I make the Date2 also read the current time?If this is vague info feel free to ask me to explain better.ThanksSunny
View 1 Replies
View Related
Apr 16, 2008
Hi guys,
I dont know whether this question sounds silly.I came up with a situation where I need to know what was the time 30 minutes before the current time.
Is there any straightforward way to get it..?
Regards
View 4 Replies
View Related
Oct 18, 2007
Hey All,
I am new to programming in SQL developer and I was hoping one of you kind salmon of knowledge could help me
I am running an SQL script every 15 minutes to pull data. I would like to insert the results into a column in a table. I have two issues:
1.How can I give the result column the current time stamp as an alias?
2.How can I add this column to the results table (I know its Alter table but do I put this into the insert sql?)
I would appreciate any help possible
Thanks
View 10 Replies
View Related
Dec 6, 2005
Is there a system word for the current date in a stored procedure or do I have to pass it in as a parameter ?
ie
BK_DateRequired <= Today
View 2 Replies
View Related
