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.
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
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?
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
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)
I require some scripting to return data which sits in a rolling weeks date range of the current week.
For example, on a Monday I want to return data from the previous day (Sunday to the previous Monday). On a Tuesday I want to return data from the previous day (Monday to the up coming Sunday). On a Wednesday I want to return data from the Monday (previous 2 days ago to the upcoming Sunday) On a Thursday I want to return data from the Monday (previous 3 days ago to the upcoming Sunday)
And so forth.
So I have my SQL script which works with static, fixed date ranges. But I need this additional dynamic date range logic built in.
At the following MDX code , I want to get the aggregate of measure only for  members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.
WITH MEMBER A AS (b)+(C) MEMBER [Measures].[Aggregate] AS Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00], ([Measures].[D])
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
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.
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 :(
Hi,I would like to have a date_last_modified field for one of my SQLtables. Instead of coding my front end to keep up with this field isthere a way to user a formula for this column in Enterprise Manager sothat each time a record is created or updated the current date/timewill be inserted/updated? I tried using getdate() but then the fieldalways has the current time, which is not what I am looking for. I amlooking for the time the row is updated or created. Is this possible?Thank you!
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
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.
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.
creating the missing records in a date/time range.
However, I need to return different groups for each span of records.
here's some data....
aaa1 aaa7 bbb2 bbb5 bbb6
The numbers are the hour of the day.
I need to return
aaa 0 0 aaa 1 1 aaa 2 0 aaa 3 0 ... bbb 0 0 bbb 1 0 bbb 2 1 ... and so on.
I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.
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).
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
Hello,I have a table that lists a number of available time slots for a party venue. I want a user to select a particular time slot and have the query show results for that time slot plus two time slots before and two time slots after the user's selection. If the user selects a time slot that is the first of the day, I still want that user to be shown 5 results including the one he selected. The same goes for the last time slot of the day. Can anyone help me with the SQL statement? Thank you very much in advance! Mark
I have a date filter, and I default it to first day this month and last day this month under relative date, when I run it it givis me error:
The Value expression for the report parameter €˜FromDate€™ contains an error: [BC30456] 'Date' is not a member of 'Integer'. (rsCompilerErrorInExpression)
select shop ,ltrim(str(datepart(hh,yourdatetimefield)))+':00 - '+ltrim(str(datepart(hh,yourdatetimefield)))+':59' as time_span ,sum(case when datediff(dd,yourdatetimefield,getdate())=0
At my office, we've been slowly working on putting together a data warehouse.
We're a financial services company and one of the services that we offer is debt collection. As far as reports go, our clients are interested in knowing how much money we collect over time. In particular, they want to know how many payments we've gotten 5, 10, and 15 months (and so on) after we receive a case. (Obviously, the 5-month payments are also included in the 10 and 15-month calculations).
When I wrote this report using our transactional database, I was completely new to SQL and the ever-resourceful Patron Saint took pity on me, so you can see a good description of the details at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78510
Now that I'm no longer a total newbie at SQL, and having been through a relatively extensive seminar on data warehousing, I've been entrusted with researching certain aspects of data warehouse development (rest easy, though, folks - the real DWH work is not being done by the very inexperienced me, but by an actual professional :) ).
My question:
how would you model this kind of "relative time" in a data warehouse? How would you display the 5-month, 10-month, and 15-month payments in a DWH? I can't really imagine that the kinds of joins necessary to do this in a transactional database would be desirable in a data warehouse.
We have the following:
1.) FACT_Payment: A fact table showing each each payment to the most detailed granularity. One attribute of this table is the payment date. Another attribute is a foreign key to the case dimension described below.
2.) DIM_Case: A dimension table showing information on each case, including the case start date. DIM_Case
3.) DIM_Date: A date dimension table.
(For added clarification: The FACT_Payment payment date has to be 5, 10, 15 months etc... after the DIM_Case start date.)
Any ideas, comments, experience with something like this?
I have a FileConsumptionDetail table which has [DataTime] every 30 minutes. Also, I have a schedule table with has range time and with certain condition.
Now I would like to query against these 2 tables what is the best approach to handle to do this due the process load is around 20,000 records. The end of result is sum of the Value1 of FileConsumptionDetail with the relevat schedule. I can probably using cursor and loop the 20,000 records of the data and check one by one whether that value is applied on that schedule table. Overkill .. I guess?
IsMonday - IsSunday is basically a flag for each day if it's a daily type of schedule. IsJan - isDec is a flag for each month if it's a monthly type of schedule.
Regarding 2nd table design, i am not sure this is a best approach though and I am happy to change if it's easy to query.
Hope this clear and I am appreciated your comment.
I have a table called Event, this table has two fields called StartDate and FinishDate. On my application I want the user to be able to search for all the events for a certain date range. I am having some trouble getting my head around the code and was wondering if someone could offer some advice.
Some more details... the user can enter a from date, a to date or both. I can have separate sql code for each situation. The event.startdate and event.finishdate are never null and may contain a time value.
Hi,I have a query which works for one day:SELECT SOME_COL AS something, SOME_COL2 AS something2 FROM myTableWHERE DATE = '2007-05-11' AND SOME_STAT 1Returnssomething something 21 23 4How do I get this to work for a date range (e.g. DATE '2007-05-09')where I get:date something something22007-05-09 1 22007-05-09 3 42007-05-10 1 22007-05-10 3 42007-05-11 1 22007-05-11 3 4Thanks in advance!
I use oledb (ACCESS database) in my application. i want to build a query to retrieve the number of Bookings from my Booking table where the appointment_date_time.timeOfADay is in range of 9am-12pm or 14pm- 7pm, that is (>= 9 and <12) or (>= 14 and < 17). Please help to build the query,
I found some query sample like:
select * from tblstudents where classID='1' and studentstartdate between ('2004-12-03') and ('2004-12-12')
or
WHERE DateField BETWEEN @StartDate AND @EndDate
But I dont want to search year and month and day, i just want to search the actual hour of a day. i am stuck with the syntax, please help
I need SQL to determine what the date range for the previous month was, ie Start date 11/01/01 for 11/30/01. The results will populate a drop down box for user queries.
hi, i'm using sql sserver 2005 and i have a time that i need to change to a datetime query (from the start of the day to the end of the day), whats the syntax for that? thx
