Is There a Function That Takes a Year, Month and Day to Create a Date in Postgresql

Is there a function that takes a year, month and day to create a date in PostgreSQL?

Needing to do this in SQL routinely usually says you have problems with your data model where you're storing dates split up into fields in the DB rather than as true date or timestamp fields, or you have serious escaping and SQL injection problems. See explanation below.

Either of the following will solve your immediate problem:

CREATE OR REPLACE FUNCTION make_date(year integer, month integer, day integer) AS $$
SELECT year * INTERVAL '1' YEAR + month * INTERVAL '1' MONTH + day * INTERVAL '1' DAY;
$$ LANGUAGE sql STRICT IMMUTABLE;

or

CREATE OR REPLACE FUNCTION make_date(year integer, month integer, day integer) AS $$
SELECT format('%s-%s-%s', year, month, day)::date;
$$ LANGUAGE sql STRICT IMMUTABLE;

but please, read on.

---

The fact that you're asking this makes me think you're probably trying to build SQL in your application like this:

$sql = "SELECT date'" + year + '-' + month + '-' + day + "';";

which is generally dangerous and wrong (though probably not directly unsafe with if year, month and day are integer data types). You should be using parameterized queries instead if this is what you're doing to avoid SQL injection and save yourself lots of hassle with escaping and literal formatting. See http://bobby-tables.com/ .

Here's how you'd query a date using a parameterized statement in Python with psycopg2 (since you didn't specify your language or tools):

import datetime
import psycopg2
conn = psycopg2.connect('')
curs = conn.cursor()
curs.execute('SELECT %s;', ( datetime.date(2000,10,05), ))
print repr(curs.fetchall());

This will print:

[(datetime.date(2000, 10, 5),)]

ie an array with a single Python date in it. You can see that it's been on a round trip through the database and you've never had to worry about PostgreSQL's date format or representation, since psycopg2 and PostgreSQL take care of that for you when you use parameterized statements. See this earlier related answer.

Producing date from year and month values in PostgreSQL

There's a '/' missing:

SELECT

   expiry_month,
   expiry_year,
   to_date(CONCAT(expiry_year, '/', expiry_month), 'YYYY/MM') AS the_start_of_year_month

FROM thisTable ;

will produce:

expiry_month | expiry_year | the_start_of_year_month
-----------: | ----------: | :----------------------
           9 |        2018 | 2018-09-01             
           1 |        2019 | 2019-01-01             
           5 |        2016 | 2016-05-01             
           3 |        2019 | 2019-03-01             
          10 |        2017 | 2017-10-01             
           2 |        2020 | 2020-02-01             

The date format is specifying '/' and it wasn't there, so, the whole text was taken as the year, and the month and day were taken as 1/1. CONCAT('2018','9') was just returning '20189' (which is a valid year).

dbfiddle here

How to extract year and month from date in PostgreSQL without using to_char() function?

date_part(text, timestamp)

e.g.

date_part('month', timestamp '2001-02-16 20:38:40'),
date_part('year', timestamp '2001-02-16 20:38:40') 

http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html

Month and year instead of the date variable

Use a character type parameter (text or varchar, not character!) and the function to_date() to convert the input to a date.

Your function, simplified and fixed:

CREATE OR REPLACE FUNCTION public.fn_reporte_venta(p_fech text
                                                 , p_client integer
                                                 , p_comprobante text)
  RETURNS TABLE(nro integer, fecha date, tipo varchar, cliente text
              , porc_igv numeric, st numeric, igv numeric, total numeric) AS
$func$
DECLARE
   v_fech date := to_date(p_fech, 'YYYY-MM-DD');  -- transform to date
BEGIN
   RETURN QUERY
   SELECT v.numero_venta,
          v.fecha_venta,
          t.descripcion,
          concat_ws(' ', c.apellido_paterno, c.apellido_materno, c.nombres) AS client,
          v.porcentaje_igv,
          v.sub_total,
          v.igv,
          v.total
   FROM   venta v 
   JOIN   cliente c USING (codigo_cliente)
   JOIN   tipo_comprobante t USING (codigo_tipo_comprobante)
   WHERE  v.estado = 'E'
   AND   (v_fech = '1111-11-11' OR v.fecha_venta = v_fech)  -- var goes here
   AND   (p_client = 0          OR c.codigo_cliente = p_client)
   AND   (p_comprobante = '00'  OR t.codigo_tipo_comprobante = p_comprobante)
   ORDER  BY 2;
END
$func$  LANGUAGE plpgsql STABLE ROWS 1000;

Always use the ISO-8601 format for date literals (YYYY-MM-DD), which is unambiguous with any locale setting. Don't fall for local syntax dialects, they break if a session should run with a different locale.

I am using a date variable in the plpgsql function, which can be assigned at declaration right away.

This expression is very versatile:

to_date(p_fech, 'YYYY-MM-DD');

to_date() allows to omit elements to the right to 1 for missing data. All of these are valid and result in '2016-01-01':

SELECT to_date('2016-01-01', 'YYYY-MM-DD') 
     , to_date('2016-1-1'  , 'YYYY-MM-DD') 
     , to_date('2016-01'   , 'YYYY-MM-DD') 
     , to_date('2016-'     , 'YYYY-MM-DD') 
     , to_date('2016'      , 'YYYY-MM-DD');

So you can pass in full dates or truncated to month or even year. You always specify a single day this way.

Or to always cover a whole month:

...
   v_fech date := to_date(p_fech, 'YYYY-MM');  -- no "-DD" truncates to month
...
   AND   (v_fech = '1111-11-01'  -- also truncated to 1st of month!
       OR v.fecha_venta >= v_fech
      AND v.fecha_venta <  v_fech + interval '1 month')
...

Why not data type character? Because that's an outdated, largely useless type, typically a misunderstanding:

• Best way to check for "empty or null value"

• Any downsides of using data type "text" for storing strings?

Also note how I replaced your verbose CASE statements with simple OR expressions.

And some other minor improvements ...

How to get year, month and day from seconds in PostgreSql?

I would like to get year, month and day separately and put these formated values to columns

Don't do that.

Use a single column of type date or timestamp, depending on your application. Not every combination of your three columns will be a valid date. But every value in a single column of type date will be a valid date.

If you need the parts of a date separately, use PostgreSQL's date/time functions.

PostgreSQL Converting Two String Date Columns (Year,Month) to One Column (YYYY/MM/DD)

demo:db<>fiddle

First step: Generating a date with to_date(): Identifier Month detects the long month name

SELECT to_date(year || '-' || month || '-01', 'yyyy-Month-dd')

Second step: Generating your expected date format with to_char()

SELECT 
    to_char(
        to_date(year || '-' || month || '-01', 'yyyy-Month-dd'),
        'yyyy/MM/dd'
    )
FROM mydates

Postgres date functions

How to write function in postgresql to get months starting date and end date on the basis of name of the month passed to it

demo:db<>fiddle

CREATE OR REPLACE FUNCTION get_month_start_and_end(_month text) 
RETURNS TABLE (start_date date, end_date date) 
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        _start_date,
        (_start_date + interval '1 month -1 day')::date
    FROM (
        SELECT 
            to_date(_month || date_part('year', CURRENT_DATE), 'MonthYYYY') AS _start_date
    ) s;
END;
$$ LANGUAGE plpgsql;

1. Get current year: date_part('year', CURRENT_DATE)
2. Get date from month and year: to_date(<month text> || <year from 1.>, 'MonthYYYY'). This gives the first of month because no day value is given
3. Calculate the end day of month as you already described
4. Give out the two columns

---

Related Topics