How do I convert a string in military time into standard time?

Question

0.00/5 (No votes)

See more:

I am getting this error ERROR: syntax error at or near "," at this line

SELECT CONVERT(VARCHAR(7),CONVERT(time, OVERLAY(departure_time,3, 0,':')), 100) INTO cancel_time

I was wondering how I could fix this error. Here is the full full function

CREATE OR REPLACE FUNCTION getCancellationTime(reserve_num int)
    returns time as
    $$
    DECLARE
    cancel_time time; --LOCAL VARIABLE
    BEGIN --casting military time string into standard time
        SELECT CONVERT(VARCHAR(7),CONVERT(time, OVERLAY(departure_time,3, 0,':')), 100) INTO cancel_time
        FROM PT.Flight_schedule_information A, PT.Reservation_Detail B
        WHERE A.flight_number = B.flight_number AND B.reservation_number = reserve_num;
        return convert(varchar(4),cancel_time + 12, 114); --return 12 hours before departure time.
    end;
    $$ language plpgsql;

What I have tried:

I researched for other sources online

Posted 21-Oct-20 19:09pm

Noneyabees

Updated 21-Oct-20 19:56pm

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-10-21T19:56:00

The OVERLAY function syntax doesn't look like that at all: OVERLAY SQL Function[^] so taht is probably what SQL is complaining about.

But ... why are you storing date and time values in strings at all? That's a beginners mistake - convert from "military time" (whatever that may be) to a UTC DATETIME value in your presentation language when it is entered, and store that in your DB. Then, when you need it it's in a consistent format that can be converted to "military time" or "regular time" as needed and compared, sorted, processes, added, whatever you need.

Storing any value that isn't a string in VARCHAR or NVARCHAR columns is always a mistake which will make your life a whole load harder, even if it does make the initial INSERT code much, much easier at start with.

How do I convert a string in military time into standard time?

1 solution

Solution 1

Add your solution here

Preview 0

How do I convert a string in military time into standard time?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us