I'm looking to calculate the number of months between 2 date time fields. Is there a better way than getting the unix timestamp and the dividing by 2 592 000 (seconds) and rounding up whithin MySQL? ...
How could I convert a year-week (eg 0852 or 200852) into a date (eg 2008-12-31 or specifically a week-ending day ie Saturday 2008-12-27 or a week-beginning day ie 2008-12-21) ? Any day of the week-ending will do, Friday, Saturday, Sunday or Monday. ...
Duplicate of http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server I have a column that tracks when things are created using a datetime, but I'd like to generate a report that groups then by day, so I need a way of nulling out the time component of a datetime column. How do...
I would like to do simple date calculations in Java. For example, compute the difference in days between to dates (having a 0 time component). Of course you could do a simple substraction of milliseconds, divided by the number of milliseconds per day, which works fine - until daylight saving times enter the scene. I am conscious that dif...
Given a date range how to calculate the number of weekends partially or wholly within that range? (A few definitions as requested: take 'weekend' to mean Saturday and Sunday. The date range is inclusive i.e. the end date is part of the range 'wholly or partially' means that any part of the weekend falling within the date range means the...
How do I add times in C#? For example: Time = "14:20 pm" + "00:30 pm" ...
How can you calculate the following Friday at 3am as a datetime object? Clarification: i.e., the calculated date should always be greater than 7 days away, and less than or equal to 14. Going with a slightly modified version of Mark's solution: def _next_weekday(day_of_week=4, time_of_day=datetime.time(hour=3), dt=None): if dt i...
Hello, how can i count the number of days between (sysdate) & a column called hiredate in PL/SQL. Thanks. ...
Hello, How can I get all the Days of the previous month in PL/SQL ? Thanks. ...
I have four fields: Start_Date = 16-Jun-10 Start_Time = 14:37 End_Date = 16-Jun-10 End_Time = 14:38 I need to calculate the difference from start to end. I tried using the to_date(Start_date || Start_Time) but keep getting this error message: ORA-01830: date format picture ends before converting entire input string 01830.00000 ...
I have a database table containing credit card records. One of the fields is a Date field. I would like to update this field by changing the year portion of the date to 2011 if the year is less than 2010. From what i have found, PLSQL has functions for time and months but nothing to do with years (to my knowledge). ...
I have an oracle time interval in a select statement like this: SELECT ... CASE WHEN date1 - date2 > 0 THEN 'No' ELSE 'YES' END This fails with an invalid data type. So I tried this, and it still fails. SELECT ... CASE WHEN date1 - date2 > (sysdate - sysdate) THEN 'No' ELSE 'YES' END Is there any way t...
I want to perform a special query on a table in my oracle database. I want the result to be sorted according to an enum that I have. the enum goes as follows: private enum days { Saturday = 1, Sunday, Monday, Tuesday, Wednesday, Thursday, } I want the result to be sorted according to this enum. ...
In Oracle, you can use ADD_Months to add months on the fly in the sql statement. What is the MS SQL version. Oracle Example Select TestDate, TestFrequency, ADD_MONTHS(TestDate, TestFrequency) AS FutureTestDate FROM Tests Source : java's website ...
Hi i have the following sql query SELECT @weekenddtm = CONVERT(VARCHAR,DATEADD(DD,(7 - DATEPART(DW,@p_end_dtm)),@p_end_dtm),111) and i tried converting it into oracle using this query v_weekenddtm := CAST(p_end_dtm + NUMTODSINTERVAL((7-TO_NUMBER(TO_CHAR(p_end_dtm,'D'))),'DAY') AS DATE); but it is giving me error..any idea how t...
I am running a query that returns me a collection of date objects for months between a certain date range. The query works fine, but is very slow (~2 seconds on my local machine, ~30 in our corporate development environment). Here it is: SELECT ADD_MONTHS(TO_DATE('200804', 'YYYYMM'), -1+rownum) AS MONTH FROM all_objects WHERE ADD_MONTHS...
I have a date in R, e.g. dt = as.Date('2010/03/17'); I would like to subtract 2 years from this date, without worrying about leap years and such issues, getting as.Date('2010-03-17'). How would I do that? Thanks! ...
This probably isn't as complicated as it should be, but Business Objects seems to be very, very strict in how types are used versus SQL Developer. Here's the piece of the statement in question: ship_date between '01' || '-' || w_current_ora_month || '-' || to_char(to_date(w_last_day, 'DD-MON-RR HH:MI:SS AM'), 'yy') and to_char(to_date(w...
I am trying to sum INTERVAL. E.g. SELECT SUM(TIMESTAMP1 - TIMESTAMP2) FROM DUAL Is it possible to write a query that would work both on Oracle and SQL Server? If so, how? Edit: changed DATE to INTERVAL ...
In Oracle BI Publisher, I can't seem to find in what format the date parameters are passed in to the query. For example select * from myTable where the_date = :the_date Any ideas? ...