i need to be able to do an sql call whereby i can check the day and hour for publishing.eg select story where day = ?? AND hour = ?? any one know the best way to do this.
i need to call out date as a day number or someting and the getdate as just the hour??? anyone know how??
Hi,
I've got a time series of the number of new customer subscriptions, which
is the target attribute to predict. The number of subscriptions depends on
various marketing activities, such as mailings, which are known within
the time series for the past.
If I train an ART (MS Autoregression Tree), it learns the trend pattern
as well as the correlations between the marketing activities and the
target (cross-correlations), right?
What I would like to do is, providing the model with some marketing activity
planning for the future and let the model predict the number of new
subscriptions based on a) the past trend pattern and b) the future activities.
Unfortunately a time series algorithm does not provide some kind of scoring for input data.
What would be the best approach to solve this problem? How about linear regression?
But how to train a regression model with trend patterns?
Thanks for your help!
i have a query i need to calulate the HH of a datetime field
example
Dateval Need to get
10/01/2007 7:48
10/01/2007 7:00
10/01/2007 7:49
10/02/2007 7:00
10/01/2007 7:50
10/03/2007 7:00
10/01/2007 7:51
10/04/2007 7:00
10/01/2007 7:52
10/05/2007 7:00
10/01/2007 7:53
10/06/2007 7:00
10/01/2007 7:54
10/07/2007 7:00
10/01/2007 7:55
10/08/2007 7:00
hello
how can i select all dates upto todays date and include the first next future date in a given data base
say todays date was the 01/06/2006 (MM,DD,YYYY)
below is a mock data base
id date (MM,DD,YYYY)
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
5 01/20/2006
i want to select all dates equal or less that 01/06/2006 and include the first next future date .. and in this case it would be 01/09/2006
so the results would return
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
i have written a sql function which returns only number of working days (excludes holidays and Weekends) between given StartDate and EndDate.
USE [XXX]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateNumberOFWorkDays] Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO
[code]...
I need a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date. Example scenario: If i give date as 12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table tblMasHolidayList where all the 2015 year holidays dates and info are stored.
How should I find the dates for 3 days past and 5 days future. Such as TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
View 5 Replies View RelatedI have had one tossed at me that I've never dealt with before. While I've done calculations on date parts I'm a little lost on how to proceed. They want a stored procedure that will take date from one field and add three days to it and that would update another field. The three days must account for weekend and holidays.
The date would be receiveddate 12/03/2007 and the due date 12/06/2007. If someone has run into this and has some sample code it would be a great help.
Any help would be appreciated.
How should I find the dates for 3 days past and 5 days future. Such as TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
View 20 Replies View RelatedI need to test an HR application with a future date without changing the system date. If I just rollback the the date fields in the data, it may not work as the application itself might be using getdate() type of functions..
Any thoughts on how to do this w/o touching the system date setting?
Thanks.
I want to round times down to the previous hour,
For example a call time of 7/31/07 11:51:13.532 would become 7/31/07 11:00:00.000.
I need to query data from a table which has order_date which is datetime field. How to pull orders by hour by day per month for the last 4 months. Any idea how to write the query?
Sample data:
order_dt order_no
2004-02-04 18:18:19.800 100
2004-02-04 18:18:22.677 101
2004-02-05 06:30:23.300 102
2004-02-05 06:31:39.533 103
2004-02-05 06:32:45.377 104
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
Time Interval for hour and 30 minutes
--------------------------------------------------------------------------------
Hi Everyone
I Have column in sql server databas as "HHMMSS" and data as and i am doing a substring to get values for hours and minutes. since my calculations based on hour interval and 30 minutes interval
for ex: Now i want to show all the transaction done b/w 6 to 7 am or pn.
and for 30 minutes interval i have get the calculation as transactions done b/w 6:00 to 6:30 and 6:30 to 7:00 either it's am or pm. now how i can write my sql statements that calculates hour and 30 minutes intervals
HHMM
-------------
06:43
09:26
09:26
11:58
12:25
18:17
20:45
00:43
00:53
16:47
Thanks
Phani
All ,
I need to convert the given time into 24 hour clock .
I have the two tables out of which one contain time in 12 Hr clock and another contain time in 24 Hr clock and i need to make a join on this colum by converting 12 Hr time in to 24 hr clock time.
Please help ..
Regards,
Ashish
field prodDate puts out values as such:
8/12/2013 7:50:15
8/12/2013 7:51:03
8/12/2013 7:53:42
8/12/2013 7:54:12
8/12/2013 7:56:02
...
...
8/12/2013 14:57:57
8/12/2013 14:59:59
I'd like to get a time span sum beginning with the first prodDate entry and ending with the last.
The idea is I'll then use that to divide lbs produced, thus giving me a gauge for lb per hour.
I have data that looks like the following.
ID Date Time Length Interval_Num
1 10/11/2014 9:00 420 14
Basically, length represents the # of minutes a person is scheduled for. We have 30 minute intervals. Interval_Num = (Length/30). I need the data to show each interval. For the above example it should only be 14 intervals.For the above example the solution should look like....
ID Date Time Length
1 10/11/2014 9:00 30
1 10/11/2014 9:30 30
1 10/11/2014 10:00 30
1 10/11/2014 10:30 30
for 14 intervals up until 16:00
How can I do this? I have a column that is a varchar and times are stored like this:
0600
1240
0145
2335
How can I get those to HH:MM formats? I've tried this but I don't know if it's the best way, plus there are seven 0s after the MM:
convert(time,LEFT(b.status_time,2) +':'+ RIGHT(b.status_time,2))
I would like to make a function to convert a datetime to half-hour. E.g. If the timestamp is 1:23:05 then converts to 1:30:00, if 1:35:27 then converts to 2:00:00.
Anyone has any idea? Thanks.
Hello people,
I'm migrating an application from asp to asp.net and access to ms sql 2005. On the old db, there's a table with a column to store date and a column to store time. Ms sql only works with date and time together, right? I created a query to get the time from time column and update the date column inserting the time. I did something like this:
UPDATE S_ACC_MONEYSET Date_Oper = CONVERT(varchar, Date_Oper, 101) + ' ' + CONVERT(varchar, Time_Oper, 108)
Does anyone knows a better way to do that? Any other comments?
Thanks!
How to get hour from a date
Msg_Time = 10/28/2006 12:20:00 AM
here how to get hour, minuts
see for month we are using month(Msg_Time),
for year we are using year(Msg_Time) same way how to fetch the hour and minutes
all are appreciate
SSRS 2008 R2. I am writing a report that uses a DB4 database. Because of this many SQL commands that I normally use are not available and I don't know what commands I can use. Because of this I am doing much of my calculations inside of SSRS.
I need to round a time field to the nearest quarter hour inside of SSRS.
For example:
8:12 becomes 8:15
2:30 becomes 2:30
1:57 becomes 2:00
4:07 becomes 4:00
from getdate(), how can i get today's date with specific time :
For example : (i just need specific 11oclock today)
2014-07-08 11:00:00.000
This do the trick :
select DATEADD( HH, 11, CAST(CAST(DATEADD(DAY,-1,GETDATE()) AS INT) AS DATETIME))
But at second half of the day it changed from 2014-07-08 11:00:00.000 to the next day, i dont know why who controls the past controls the future, who controls the present controls the past. ¯(º_o)/¯ ~~~
I have a Date column in my table.I want to insert the Date and hour in that Column.
Here is the sample data 2011/11/25 11 AM
The Text in Bold i.e 11 Am is how the hour part i want.
I know how to do this in Excel, but not sure about Power Pivot. Our fire department works on a 24 hour shift that starts at 0700 each morning. I want to look up what shift is working depending on the date and time of dispatch. In Excel, I would do a look up table, but instead of using false() as the last parameter, I would choose true().
I am not sure if this would be best created as a calculated column in the table that holds the incident date and time values, or have a separate table and create a relationship to use the shift in the rows/columns of my pivot table.
Has anyone here been involved in a successful commercial Data Warehouse project?
If so, what was your role? How big was the project? Did you do it all or was their one guy data modelling, one for ETL, one for Client-side, one project managing, etc...? What tools did you use? Was it in SQL2000? 2005? Did you use DTS/SSIS for your ETL or Sprocs? Did you use SSAS? Were you writing lots of MDX to meet client-side needs? Or did you use a completely different toolset for all or some of this?
Did you use the Kimball approach? Or were you in the other camp?
Was your latency nightly or did you have some type of "real-time" capability?
I would prefer to hear from people who've actually been involved in successful projects (or failed projects if you would like to share with us why you think they failed). I'm not too interested in hearing from people that have never done it but have a theory as to how it should work (like me).
Many Thanks,
Coolerbob
(Or is there some other website forum I should be asking this question on? (like mdxteam, olapteam, datawarehouseteam or something)
Hi All,
i want to substracting two dates but the results in hh:mm format.
anybody know how to do that?
thanks!
Addin
How I can update the following date to get the desired result with specific hour, minute and second
2014-01-01 00:00:00.000
Desired Result
2014-01-01 17:20:20.000
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
I have a request where i would like to get the start date/time and end date/time and flag (with an int) which hours (24 hour clock) have values between the two dates. Example car comes into service on 2013-12-25 at 0800 and leaves 2013-12-25 at 1400 the difference is 6 hours and i need my table to show
Column: Hour_6 Value: 0
Column: Hour_7 Value: 0
Column: Hour_8 Value: 1
Column: Hour_9 Value: 1
Column: Hour_10 Value: 1
Column: Hour_11 Value: 1
Column: Hour_12 Value: 1
Column: Hour_13 Value: 1
Column: Hour_14 Value: 0
As i'm working away at it i'm trying to figure out how i could use a Time Dimension table for this but dont really see much. So far i have the difference between the two times in hours (hour_diff) and the start hour (min_hour) so i would like to do something where i update the first hour (min_hour) and update columns based on the numbers of hours (hour_diff)
I just need the date, hour, and minute...not the micro seconds. Do I have to DATEPART and concatenate each or is there any way to simply truncate the milliseconds from it? Or is there a date format to put extract and report on it as...
MM/DD/CCYY HH:MM:SS AM
I see there is a format 131 that puts it in..
DD/MM/YYYY HH:MM:SS:MMMAM
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.
I have a table that is recording hits to a website. Everytime someone views a page, the datetime of the hit is recorded in a field called hit_date_time. I would like to be able to come up with a query that will show how many hits occured on a given day or given days, broken down by hour.
The resulting table for two days would look something like:
Time Hits
1/1/01 12:00 1
1/1/01 1:00 23
1/1/01 2:00 54
1/2/01 1:00 15
1/2/01 2:00 14
I can't seem to figure out how to write the query so that I can take into consideration the date and hour of the event so that I can count it.
Thanks,
Eron
I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
How can i do this?