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


PLSQL Need REFCURSOR DATE + TIME

Question:

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?

SET SERVEROUTPUT ON;
CREATE OR REPLACE TYPE t_openingstijd IS TABLE OF DATE;

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)
AS
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;
BEGIN
-- 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;
END IF;

-- 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;
            ELSE
                v_eindMinuten := 45;
            END IF;

            v_eindUur := v_eindUur - 1;
        ELSE
            IF(TO_NUMBER(TO_CHAR(i_tijd.gesloten, 'MI')) < 45) THEN
                v_eindMinuten := 0;
            ELSE
                v_eindMinuten := 15;
            END IF;
        END IF;
    ELSE
        IF(TO_NUMBER(TO_CHAR(i_tijd.gesloten, 'MI')) < 15) THEN
            v_eindMinuten := 45;
            v_eindUur := v_eindUur - 1;
        ELSE
            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;
            ELSE
                v_eindMinuten := 30;
            END IF;
        END IF;    
    END IF;

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

    IF(TO_NUMBER(TO_CHAR(i_tijd.open, 'MI')) < 15) THEN
        v_beginMinuten := 0;
    ELSIF(TO_NUMBER(TO_CHAR(i_tijd.open, 'MI')) < 30) THEN
        v_beginMinuten := 15;
    ELSIF(TO_NUMBER(TO_CHAR(i_tijd.open, 'MI')) < 45) THEN
        v_beginMinuten := 30;
    ELSE
        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.extend;
            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
                v_besteltijden.extend;

                IF(i = 1) THEN
                    v_besteltijden(v_besteltijden.count) := TO_DATE( v_beginUur || ':00', 'HH24:MI');
                ELSE
                    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
            v_besteltijden.extend;

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

END LOOP;

-- 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
        IF(TO_NUMBER(TO_CHAR(SYSDATE, 'HH24MI')) >= TO_NUMBER(TO_CHAR(i_openingstijd.open, 'HH24MI'))
          AND TO_NUMBER(TO_CHAR(SYSDATE, 'HH24MI')) <= TO_NUMBER(TO_CHAR(i_openingstijd.gesloten, 'HH24MI'))) THEN
            o_open := true;
        ELSE
            o_open := false;
        END IF;
    END IF;
END LOOP;

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

Answer:

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.


Related:


Using VLOOKUP formula or other function to compare two columns


mysql,excel,vba,date
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...

Android - At Minutes to a Time hh.mm.ss


java,android,date,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"...

How to set current date with HTML input type Date Tag


php,html,date
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?...

Get n data based on the same day (timestamp)


mysql,timestamp,date
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...

convert date format in php [duplicate]


php,mysql,date
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...

Remove leading zeros from Oracle date


sql,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 ...

IBM Cognos _days_between function not working


mysql,database,date,cognos
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...

Creating javascript dates object


javascript,date,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);...

Count Down 1 Day for Each Row mysql


mysql,date
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...

Convert double to time_t


c++,time,standards,chrono,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?...

reading OData date format in R


r,date,odata
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?...

Formatting dates in an MVC Dropdown


date,model-view-controller,html-select
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...

Format date (with different input) php


php,date,format,multilingual,jquery-validation-engine
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...

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


c#,asp.net,sql-server,date,gridview-sorting
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...

PHP Checking if user is online


php,date,datetime,timestamp
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...

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


javascript,jquery,date
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...

Select before specific date from one day only


mysql,sql,date
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...

Add days, weeks, months to date using jQuery


jquery,date
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...

Using date in CreateQueryDef


vba,date,ms-access
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 |...

Convert string to ISODate in MongoDB


javascript,python,mongodb,date
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...

Time format conversion with PHP


php,time
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....

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


time,smallbasic
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....

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


javascript,jquery,google-chrome,date,momentjs
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...

convert time to to 12 hour by manipulate string


javascript,jquery,datetime,time
"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] +...

How to convert date into timeago


javascript,html5,time,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(message.date)); userSpan.appendChild(timeTag); This javascript will genearate the following code <span...

Convert March 2015 to 03/2015 and make into date


mysql,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...

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


php,datetime,time
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...

Elasticsearch NumberFormatException when running two consecutive java tests


java,date,elasticsearch,numberformatexception,spring-data-elasticsearch
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,...

How to get dates between two dates


php,date
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...

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


linux,bash,date,scripting
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...

JSON Date Formatting


jquery,json,date
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...

AppleScript (or swift) add hours to time


swift,date,applescript
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...

Convert time based on timezone using java.time


java,time,java-8,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...

Is timezone info redundant provided that UTC timestamp is available?


datetime,time,timezone,timestamp,timestamp-with-timezone
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...

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


java,sqlite,date,jdbc
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...

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


r,date,csv
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...

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


python,date,select,netcdf
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...

How to Minus time with python


python,time
I'd like to get the time before X seconds before datetime.time.now(). For example if the time.now() 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....

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


php,date,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...

need the way to put Date in Parse.com without Time and the opposite


android,date,time,parse.com
Can't figured how to put just Date or just Time to Parse.com. 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) { ......

Get next month using mktime()


php,date,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...

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


python,multithreading,events,time,condition
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...

Convert long int seconds to double precision floating-point value


c++,date,datetime,time,converter
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...

PHP Timezone not working correctly


php,date,time,timezone
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...

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


excel-vba,date,format
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...

How to change date-time format?


sql-server,date,datetime,sql-server-2014
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...