date,time,plsql,cursor , PLSQL Need REFCURSOR DATE + TIME



Tag: date,time,plsql,cursor

im using this code and I had 1 problem, the o_besteltijden cursor has this as output: 01-06-15 ( a date), but I prefer output like: 16:00 (so only time) Or 01-06-15 16:00:00 (date + time). is that possible?


CREATE OR REPLACE PROCEDURE zoekMogelijkeBesteltijden(p_winkelId IN INTEGER, p_datum IN DATE, p_periode IN INTEGER DEFAULT 21, p_bezorgen IN BOOLEAN,
                                                  o_open OUT BOOLEAN, o_besteltijden OUT SYS_REFCURSOR)
v_foundWinkel NUMBER := 0;
v_winkel Winkel%ROWTYPE;

TYPE arrayVarchar IS VARRAY(7) OF VARCHAR2(2);
v_dagen arrayVarchar := arrayVarchar('ma', 'di', 'wo', 'do', 'vr', 'za', 'zo');

CURSOR v_openingstijden(p_id IN NUMBER, p_dag IN VARCHAR2) IS
    SELECT * FROM Openingstijd
    WHERE winkel_id = p_id
    AND dag = p_dag;

v_besteltijden t_openingstijd := t_openingstijd();

v_eindUur NUMBER := 0;
v_eindMinuten NUMBER := 0;

v_beginUur NUMBER := 0;
v_beginMinuten NUMBER := 0;

-- Exceptions
v_winkelNotFound EXCEPTION;
-- Kijken of winkel wel bestaat.
SELECT COUNT(1) INTO v_foundWinkel
FROM Winkel
WHERE id = p_winkelId;

IF(v_foundWinkel = 0) THEN
    RAISE v_winkelNotFound;

-- Alle data krijgen van de winkel. Exclusief de openingstijden, producten en coupons.
SELECT * INTO v_winkel
FROM Winkel
WHERE id = p_winkelId;

FOR i_tijd IN v_openingstijden(p_winkelId, 
                               v_dagen(TO_NUMBER(TO_CHAR(p_datum, 'D')))) LOOP        

    -- Instellen van huidige tijd om mee door te lopen.
    v_eindUur := TO_NUMBER(TO_CHAR(i_tijd.gesloten, 'HH24'));

    IF(p_bezorgen = true) THEN
        IF(TO_NUMBER(TO_CHAR(i_tijd.gesloten, 'MI')) < 30) THEN
            IF(TO_NUMBER(TO_CHAR(i_tijd.gesloten, 'MI')) < 15) THEN
                v_eindMinuten := 30;
                v_eindMinuten := 45;
            END IF;

            v_eindUur := v_eindUur - 1;
            IF(TO_NUMBER(TO_CHAR(i_tijd.gesloten, 'MI')) < 45) THEN
                v_eindMinuten := 0;
                v_eindMinuten := 15;
            END IF;
        END IF;
        IF(TO_NUMBER(TO_CHAR(i_tijd.gesloten, 'MI')) < 15) THEN
            v_eindMinuten := 45;
            v_eindUur := v_eindUur - 1;
            IF(TO_NUMBER(TO_CHAR(i_tijd.gesloten, 'MI')) < 30) THEN
                v_eindMinuten := 0;
            ELSIF(TO_NUMBER(TO_CHAR(i_tijd.gesloten, 'MI')) < 45) THEN
                v_eindMinuten := 15;
                v_eindMinuten := 30;
            END IF;
        END IF;    
    END IF;

    -- Begin tijd berekenen
    v_beginUur := TO_NUMBER(TO_CHAR(, 'HH24'));

    IF(TO_NUMBER(TO_CHAR(, 'MI')) < 15) THEN
        v_beginMinuten := 0;
        v_beginMinuten := 15;
        v_beginMinuten := 30;
        v_beginMinuten := 45;
    END IF;

    -- Eerste uur vol maken.
    IF(v_beginMinuten != 0) THEN
        FOR i IN 1 .. ((60 - v_beginMinuten) / 15) LOOP
            v_besteltijden(v_besteltijden.count) := TO_DATE( v_beginUur || ':' || v_beginMinuten * i, 'HH24:MI');
        END LOOP;

        v_beginUur := v_beginUur + 1;
        v_beginMinuten := 0;
    END IF;

    -- Tot het laatste uur volmaken.
    IF(v_beginUur != v_eindUur) THEN
        FOR x IN 1 .. (v_eindUur - v_beginUur) LOOP
            FOR i IN 1 .. 4 LOOP

                IF(i = 1) THEN
                    v_besteltijden(v_besteltijden.count) := TO_DATE( v_beginUur || ':00', 'HH24:MI');
                    v_besteltijden(v_besteltijden.count) := TO_DATE( v_beginUur || ':' || 15 * (i - 1), 'HH24:MI');
                END IF;
            END LOOP;

            v_beginUur := v_beginUur + 1;
        END LOOP;
    END IF;

    -- Laatste uur ook maar eens volmaken.
    IF(v_beginUur = v_eindUur AND v_eindMinuten >= v_beginMinuten) THEN
        FOR i IN 1 .. (v_eindMinuten / 15) + 1 LOOP

            IF(i = 1) THEN
                v_besteltijden(v_besteltijden.count) := TO_DATE( v_beginUur || ':00', 'HH24:MI');
                v_besteltijden(v_besteltijden.count) := TO_DATE( v_beginUur || ':' || 15 * (i - 1), 'HH24:MI');
            END IF;
        END LOOP;
    END IF;


-- Data terug in output cursor.
OPEN o_besteltijden FOR
    SELECT * FROM TABLE(CAST(v_besteltijden AS t_openingstijd));

-- Is de winkel momenteel open?
FOR i_openingstijd IN v_openingstijden(p_winkelId, 
                               v_dagen(TO_NUMBER(TO_CHAR(SYSDATE, 'D')))) LOOP  
    IF(o_open = true) THEN
          AND TO_NUMBER(TO_CHAR(SYSDATE, 'HH24MI')) <= TO_NUMBER(TO_CHAR(i_openingstijd.gesloten, 'HH24MI'))) THEN
            o_open := true;
            o_open := false;
        END IF;
    END IF;

WHEN v_winkelNotFound THEN
    dbms_output.put_line('Winkel niet gevonden.');


The problem here seems to be as follows:

  1. You are setting o_besteltijden as a value selected from CAST(v_besteltijden AS t_openingstijd) where v_besteltijden is calculated from to_date

  2. t_openingstijd is a table of date type (CREATE OR REPLACE TYPE t_openingstijd IS TABLE OF DATE;). So your answer will be in date format.

Instead if you need the timestamp type you have to make changes to these variables and get timestamp stored in them to get the output as desired date+time or time through formatting.


need the way to put Date in without Time and the opposite

Can't figured how to put just Date or just Time to Even after SimpleDateFormat the Date objects transferring with full default missing data. Code samples: DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy"); DateFormat timeFormat = new SimpleDateFormat("HH:mm"); try { date = dateFormat.parse(mDate.getText().toString()); time = timeFormat.parse(mTime.getText().toString()); } catch (java.text.ParseException e) { ......

How to change date-time format?

I have a RegDate column of nvarchar(max) type in my table in which dates are stored in mm/dd/yyyy (5/22/2015 11:09:39 PM) and dd-mm-yyyy (19-05-2015 22:55:05) format. I want to get all these entries in one format i.e. dd/mm/yyyy. I tried to convert it by using Convert(varchar(10),cast(vr.RegDate as DATETIME),105) as RegistrationDate...

Is timezone info redundant provided that UTC timestamp is available?

I have a simple mobile app that schedules future events between people at a specified location. These events may be physical or virtual, so the time specified for the event may or may not be in the same timezone as the 'location' of the event. For example, a physical meeting...

Get n data based on the same day (timestamp)

I have this table "sensor" which has these fields : id (int,PK) timestamp (timestamp) latitude (varchar) longitude (varchar) data (float) As in one query I want to get 5 rows that were on the same day. Please note that it's not current date and the data has lots of different...

Count Down 1 Day for Each Row mysql

I added a column to my mysql table called dateAdded which should hold a time value for each row. I want to add a time value into each row in the column, but I want it to count down by one day. I used: UPDATE products set dateAdded=DATE_ADD('2015-06-15 11:31:31', INTERVAL...

Android - At Minutes to a Time

i,ve got a problem which i cant solve at the moment. I already googled for it and found some solutions, but all of them not rly match my problem. So i get 2 Strings from SQL Database in the format hh:mm:ss. The first String is a time, for example "14:30:00"...

JSON Date Formatting

I'm having an issue with the date format when getting JSON Date/Time. I've managed to get the date format to: 2009-06-25T17:32:10.0000000 But I need to be: 25/06/2009 17:32:10 But since I'm getting this data from a loop I've trouble finding out how exactly to format the date so that the...

How to set current date with HTML input type Date Tag

I am new in php and i want to set current date in my HTMl date tag <input type="date" value="Today"> How i can get current date?...

Convert double to time_t

I have a double containing seconds. I would like to convert this into a time_t. I can't find a standard function which accomplishes this. Do I have to fill out the time_t by hand?...

Get next month using mktime()

I'm trying to get the next month using PHP mktime() function. I am looking to get a result like 07/2015 but instead it is giving me 01/2015. Here is the code I am using: $next_month = strftime('%d/%Y', strtotime('+1 month', mktime(0,0,0,$month,1,$year))); The value of $month is 06. The value of $year...

display message if time difference is greater than 50 minutes - php

Basically i'm getting time difference between current time and database time. $cur_time = time(); $db_time = $rs[$k]['update_time']; $diff = abs($cur_time - $db_time); $months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24)); $days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24)); $hours = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24...

How to Minus time with python

I'd like to get the time before X seconds before For example if the is 12:59:00 so If I minus 59, I want to get 12:00:00. I can't find a way to do that. Any help will be appreciated....

python - how to check whether some given date exists in netcdf file

I read my timesteps from a netcdf file: timesteps = ncfile.variables['time'] Now I would like to know whether some given date (say, June 23th) exists in that list. If it does, I want to omit it. The netcdf file contains arbitrary dates. I have no clue how to do this...

Create a variable Date in format “yyyy-mm-dd”

I am trying to insert to my table, a date in format "yyyy-mm-dd". My table has a field date type DATETIME and I use SQLite DB SYSTEM. I need to be in this format, so later to have the option to select dates from - then. My table: CREATE TABLE...

Select before specific date from one day only

I need to retrieve data from MySQL from one day, but I don't know the interval between two rows (there may be 4 rows from one day and a gap of a week for example). Let's say I have: (following isn't code) 3 rows where date = 2015-06-15 1 row...

Format date (with different input) php

I am working on a multilingual formular and have to solve a date formatting problem: In German the date format is: DD-MM-YYYY While in another country e.g. England the date format is: YYYY-MM-DD While this input validation is handled right, I need to get a easy solution for my php...

How do I change chromes Date-parsing / Sorting to match ff/ie Dateparsing?

So I encounter a very weird problem here and am out of ideas at the moment. I've the following function to parse and sort a json object by its property RowDate, containing datestrings with the following format: "/Date(1389682861507+0100)/". orderByDate: function (json) { debug('[DataCollection::orderByDate], json before ordering: ', json); _.each(json.LineDefinitions, function...

Time format conversion with PHP

If have the duration for a recipe in the format 1H10M (1 hour, 10 minutes) or 20M (20 minutes). I want to use the format as described in the parentheses. I've tried using strtotime() without luck. Any help would be greatly appreciated....

convert time to to 12 hour by manipulate string

"2015-06-23 14:00:00" I tried to format above date time into 12 hour base but stuck in somewhere. function formatDate(raw_date){ var right = raw_date.substring(10, 0); var hours = ((right[0].substring(2,0) + 11) % 12 + 1); var min = raw_date.substring(14,16); var suffix = right[1] >= 12 ? "PM":"AM"; right[1] = ((right[1] +...

Remove leading zeros from Oracle date

I want to output the current date in the format: 1-1-2015 So without the zero's in this example: SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY') AS today FROM dual Output: 17-06-2015 ...

Convert time based on timezone using java.time

How to change the time based on timezone in LocalDateTime, here i have built a date with Time zone as EST, now i need to find the UTC of the corresponding time. please help me how to solve this String str = "16Jun2015_153556"; DateTimeFormatter formatter = DateTimeFormatter.ofPattern("ddMMMyyyy_HHmmss"); formatter.withZone(ZoneId.of("EST5EDT")); LocalDateTime dateTime...

How to validate an input date from individual day, month, year input in Laravel 5

I have a user registration form which takes input birth day split in 3 different input fields i.e. day, month, year {!! Form::selectMonth('month', null) !!} {!! Form::selectRange('day', 1, 31, null) !!} {!! Form::selectYear('year', Carbon\Carbon::now()->year, (new Carbon\Carbon('100 years ago'))->year, null) !!} In the backend I have modified the Registrar.php/validator() to check...

How do you start a function at a certain time in Python?

I have a list of timestamps, and I need to call a specific method when those timestamps are reached. Those timestamps are roughly 20ms apart. At the moment I am using busy waiting in a separate thread, but I am worried about the CPU overload. Example : while True: if...

SQL Server / C# : Filter for System.Date - results only entries at 00:00:00

I have a connected SQL Server database in Visual Studio and am displaying its content in a grid. I created a dropdown menu with the column names as selectable options and a text field to filter for specific content, e.g., DropDown = "Start" - Textfield = 14.03.2015 = Filter Column...

AppleScript (or swift) add hours to time

I'm trying to add 8Hours to a date (from the clipboard) set date1 to the clipboard set newdate to date1 + (8 * hours) display dialog "Purchases were downloaded at " & newdate buttons {"OK"} default button 1 But this is not working as expected, I'm having the error Can’t...

Using date in CreateQueryDef

I have a table on which I run a query that I export to Excel. Here is an example: Table: Food Item | Price | Limit_Date | ------------------------------- Carrot | 0.80 | 08/07/2015 | Salmon | 4.30 | 01/07/2015 | Biscuits | 2.40 | 15/12/2015 | Milk | 1.00 |...

Add days, weeks, months to date using jQuery

I have a form which lets the user select an invoice date and payment terms. The payment terms are; 1 week from invoice date, 4 weeks from invoice date, 1 month from invoice date, etc. When the user has selected an invoice date and the payment terms I then want...

Convert long int seconds to double precision floating-point value

I have a long int variable wich containes seconds since Jan. 1, 1970 in this format: long int seconds = 1231241242144214; i need to convert this seconds to double precision floating-point value. The integer part of the value is the number of days since midnight, 30 December 1899. The fractional...

How to convert date into timeago

I am trying to convert a date field in a "timeago" format using jquery.timeago.js $("time.timeago").timeago(); var userSpan = document.createElement("span"); userSpan.setAttribute("class", "text-muted"); userSpan.appendChild(document.createTextNode(message.usernameSender +" | ")); var timeTag = document.createElement("time"); timeTag.setAttribute("class", "timeago"); timeTag.setAttribute("datetime",document.createTextNode(; userSpan.appendChild(timeTag); This javascript will genearate the following code <span...

convert date format in php [duplicate]

This question already has an answer here: Convert one date format into another in PHP 9 answers Convert date format yyyy-mm-dd => dd-mm-yyyy 11 answers I would like to know how to convert 2015-06-15 to June 15, 2015 in PHP This is essentially the format that it is stored...

Date formatting - making dd/m/yyyy into dd/mm/yyyy

I got a pretty simple question (but yet I've been stuck at it for some time now). Does anyone know how to make the date value from dd/m/yyyy into dd/mm/yyyy in a variable? dim lastdaylastmonth as date lastdaylastmonth = DateSerial(Year(Date), Month(Date), 0) So this code, as of now, would return...

reading OData date format in R

I am reading a csv file exported from OData, and encounter a date format that looks like this: /Date(1391514600000)/. Any ideas on how to convert this into Date or POSIX in R?...

Convert strings of data to “Data” objects in R [duplicate]

This question already has an answer here: as.Date with dates in format m/d/y in R 2 answers My problem is that the as.Date function does not convert the values in a "date" column of a data frame into Date objects. I have a data.frame nmmaps. Here is a short...

Using VLOOKUP formula or other function to compare two columns

I have one table like this: SHORT TERM BORROWING 1/6/2009 94304 12/31/2010 177823 6/30/2011 84188 12/31/2011 232144 6/30/2012 94467 9/30/2012 91445 12/31/2012 128523 3/31/2013 83731 6/30/2013 78330 9/30/2013 70936 12/31/2013 104020 3/31/2014 62345 6/30/2014 62167 9/30/2014 63494 12/31/2014 104239 3/31/2015 69056 I have another column which lists each date from...

Creating javascript dates object

I am trying to create an object with all the dates from "today" until 60 days from now. My code: var dates = []; getDateRange(); function getDateRange() { var today = new Date(); var date = new Date(); for (var i = 0; i <= 59; i++) { date.setDate(today.getDate()+i); console.log(date);...

Small Basic - How to check if a date is passed?

I'm trying to make a license verification that should expire in a week with small basic. I could make a script that would write down the day the license was activated and that would expire the license as soon as the current day is 7 larger than the written amount....

Formatting dates in an MVC Dropdown

I have a dropdown list on my page as so: - @Html.DropDownList("dd_dates", new SelectList(@Model.seasonDates), "Please Select") where seasonDates is an IList of dates. The problem is that when the dates are displayed in the dropdown they also have the time on them as well. How do I format the display...

PHP Checking if user is online

I don't fully understand the PHP time functions, but I'm under the impression my function should work: function user_status($timestamp){ $online = date('Y-m-d H:i:s', strtotime('-5 minutes', strtotime(date('Y-m-d H:i:s')))); $away = date('Y-m-d H:i:s', strtotime('-15 minutes', strtotime(date('Y-m-d H:i:s')))); $offline = date('Y-m-d H:i:s', strtotime('-30 minutes', strtotime(date('Y-m-d H:i:s')))); if(strtotime($timestamp) >= $online){ return 'online'; } else...

What are valid input DATE formats for the (Linux) date command?

The following command generates the date for the next day: date -d "20150615 12:00 +1 day" +%Y%m%d 20150616 I would like to specify my own INPUT date format, such as: 2015_06_15 But the date command does not like this format and complains about invalid date: date: invalid date '2015_06_15 12:00...

IBM Cognos _days_between function not working

I'm having an issue with Cognos 10. I'm trying to calculate the number of days between to dates so I use the _days_between( date1, date2 ) function. _days_between ([Derniere Date Changement diaphragme] , [Premiere Date Changement diaphragme]) I'm quite sure the two Dates are Date objects ( I set them...

How to get dates between two dates

List of Dates between my From and Two <?php $scheduleStartDate = 2015-06-20; $scheduleEndDate = 2015-06-25; $Date = getDatesFromRange($scheduleStartDate,$scheduleEndDate); $Date = substr($Date, 0, -1); function getDatesFromRange($start, $end){ $dates = array($start); $Value = ''; while(end($dates) < $end) { $dates[] = date('Y-m-d', strtotime(end($dates).' +1 day')); $Value .= '"'.date('j-n-Y', strtotime(end($dates).' +1 day')).'",'; } return...

Convert March 2015 to 03/2015 and make into date

I have a column which holds values in the format 'June 2015', 'July 2015', 'March 2014', 'February 2016'. I need to convert these into the format 06/2015, 07/2015, 03/2014, 02/2016.. but also make them a date so I can sort by this column. I currently have the following select: SELECT...

Elasticsearch NumberFormatException when running two consecutive java tests

I have two test in a class, each of them containing the following query: SearchQuery searchQuery = new NativeSearchQueryBuilder().withQuery(matchAllQuery()).withFilter(rangeFilter("publishDate").lt(date)).build(); In one of the tests, the number of the results elasticsearchTemplate.count(searchQuery, Article.class), in the other one the returned values are verified elasticsearchTemplate.queryForPage(searchQuery,Article.class) If I run any of these two tests separately,...

PHP Timezone not working correctly

So we have in-house servers here in this EDT part of the USA America/New_York and for someone reason, this [SSCCE][1] code is not working. <?php echo date_default_timezone_get() . " <br />"; $dateCal = "2015-06-22 13:00:00"; // EDT Time $schedule_date = new DateTime($dateCal, new DateTimeZone("America/Chicago") ); echo $dateCal . " <br...

How to find the days b/w two long date values

I have two long dates value. For Ex: 1433097000000 1434479400000 Here i have to find the days between these two by jquery For Ex: 15/06/2015 - 22/06/2015 If it is more than 5 days, I want to get from is 15/06/2016, to is 19/06/2015. It is based on long values...

Convert string to ISODate in MongoDB

I am new to MongoDB and I am stuck on the String to Date conversion. In the db the date item is stored in String type as "date":"2015-06-16T17:50:30.081Z" I want to group the docs by date and calculate the sum of each day so I have to extract year, month...