set variable problem with date type conversion

[lozdown]

I am using postgresql, pgadmin4 v9.3 and SQLPage v0.34.0

Calling sqlpage file with this url http://localhost:8080/do_report?SDate=1/4/25&EDate=30/4/25&PDate=

In this file I have this code

set EndDate = 
  case 
     when $id is not null
         then (select "End" from reports  where id=$id::int)
     else
        coalesce($EDate,'1/1/20')::date
  end;

If $EDate='30/4/25' the set operation fails with error

date/time field value out of range: "30/4/25"

If $EDate='25/4/30' or $EDate='4/30/25' the set operation works correctly.
I am thinking there is something curious about the date conversion when using UK format dates going via SQLPage

If I put the sql shown in the error message

SELECT CASE WHEN CAST($1 AS TEXT) IS NOT NULL THEN (SELECT "End" FROM reports WHERE id = CAST($1 AS TEXT)::INT) ELSE coalesce(CAST($2 AS TEXT), '1/1/20')::DATE END AS sqlpage_set_expr
into pgadmin and manually substitute the text parameters giving this sql

SELECT CASE WHEN CAST(null AS TEXT) IS NOT NULL THEN (SELECT "End" FROM reports WHERE id = CAST(null AS TEXT)::INT) ELSE coalesce(CAST('30/4/25' AS TEXT), '1/1/20')::DATE END AS sqlpage_set_expr
pgadmin executes the code correctly

[lovasoa]

This is a classic date format localization issue in PostgreSQL. Here's what's happening:

The Problem

PostgreSQL's date parsing behavior depends on the datestyle setting. When you run SQL directly in pgAdmin, it's using your local PostgreSQL session settings, but SQLPage may be connecting with different locale/datestyle settings.

The format 30/4/25 is ambiguous - it could be:

• DD/MM/YY (UK format) = April 30, 2025
• MM/DD/YY (US format) = Invalid (no 30th month)

Solutions

Option 1: Use Unambiguous Date Format (Recommended)

Change your URL to use ISO format or a clearly unambiguous format:

http://localhost:8080/do_report?SDate=2025-04-01&EDate=2025-04-30&PDate=

Then your SQL becomes:

set EndDate = 
  case 
     when $id is not null
         then (select "End" from reports where id=$id::int)
     else
        coalesce($EDate,'2020-01-01')::date
  end;

Option 2: Explicit Date Parsing

Use PostgreSQL's to_date() function with explicit format:

set EndDate = 
  case 
     when $id is not null
         then (select "End" from reports where id=$id::int)
     else
        coalesce(to_date($EDate, 'DD/MM/YY'), '2020-01-01'::date)
  end;

Option 3: Set DateStyle in SQLPage Connection

You can set the datestyle in the connection initialization script. In sqlpage/on_connect.sql:

SET datestyle = 'DMY'; -- Day-Month-Year format

Option 4: Handle Multiple Formats

For maximum flexibility, you could create a function to try multiple formats:

set EndDate = 
  case 
     when $id is not null
         then (select "End" from reports where id=$id::int)
     else
        coalesce(
          case 
            when $EDate ~ '^\d{4}-\d{2}-\d{2}$' then $EDate::date
            when $EDate ~ '^\d{1,2}/\d{1,2}/\d{2}$' then to_date($EDate, 'DD/MM/YY')
            else '2020-01-01'::date
          end,
          '2020-01-01'::date
        )
  end;

Recommendation

I'd strongly recommend Option 1 - using ISO date format (YYYY-MM-DD) in your URLs. This is:

• Unambiguous across all locales
• Standard format for web applications
• Doesn't require complex parsing logic
• Works consistently regardless of database settings

The discrepancy you're seeing between pgAdmin and SQLPage is likely due to different datestyle settings in their respective database connections.

See:

• https://www.postgresql.org/docs/7.2/sql-set.html
• https://www.postgresql.org/docs/7.1/datatype-datetime.html

[lozdown]

Obvious when explained - thank you so much