Convert SQL Functions from SQL Server to PostgreSQL

The list below illustrates how to convert the most popular SQL Server built-in functions into PostgreSQL equivalents:

• Function CHARINDEX must be reiplaced by PostgreSQL equivalent POSITION
• Function CONVERT must be migrated into PostgreSQL according to the rules specified in this article
• Function DATEADD($interval, $n_units, $date) can be converted in PostgreSQL expressions that use operator '+' depending on $interval value as follows:

  SQL Server Interval		PostgreSQL Equivalent
  DAY/DD/D		($date + $n_units * interval '1 day')::date
  DAYOFYEAR/DY/Y		($date + $n_units * interval '1 day')::date
  HOUR/HH		($date + $n_units * interval '1 hour')::date
  MINUTE/MI/N		($date + $n_units * interval '1 minute')::date
  MONTH/MM/M		($date + $n_units * interval '1 month')::date
  QUARTER/QQ/Q		($date + $n_units * 3 * interval '1 month')::date
  SECOND/SS/S		($date + $n_units * interval '1 second')::date
  WEEK/WW/WK		($date + $n_units * interval '1 week')::date
  WEEKDAY/DW/W		($date + $n_units * interval '1 day')::date
  YEAR/YY		($date + $n_units * interval '1 year')::date

• Function DATEDIFF($interval, $date1, $date2) of SQL Server can be emulated in PostgreSQL via DATE_PART as follows:

  SQL Server Interval		PostgreSQL Equivalent
  DAY/DD/D		date_part('day', $date2 - $date1)::int
  DAYOFYEAR/DY/Y		date_part('day', $date2 - $date1)::int
  HOUR/HH		24 * date_part('day', $date2 - $date1)::int + date_part('hour', $date2 - $date1)
  MINUTE/MI/N		1440 * date_part('day', $date2 - $date1)::int + 60 * date_part('hour', $date2 - $date1) + date_part('minute', $date2 - $date1)
  MONTH/MM/M		(12 * (date_part('year', $date2) - date_part('year', $date1))::int + date_part('month', $date2) - date_part('month', $date1))::int
  SECOND/SS/S		86400 * date_part('day', $date2 - $date1)::int + 3600 * date_part('hour', $date2 - $date1) + 60 * date_part('minute', $date2 - $date1) + date_part('second', $date2 - $date1)
  WEEK/WW/WK		TRUNC(date_part('day', $date2 - $date1) / 7)
  WEEKDAY/DW/W		date_part('day', $date2 - $date1)::int
  YEAR/YY		(date_part('year', $date2) - date_part('year', $date1))::int

• Every occurrence of DATEPART must be replaced by DATE_PART
• SQL Server function GETDATE must be converted into PostgreSQL NOW()
• Every occurrence of ISNULL must be replaced by COALESCE
• SQL Server function REPLICATE must be converted into PostgreSQL equivalent REPEAT
• Every occurrence of SPACE($n) must be replaced by REPEAT(' ', $n)

Have questions? Contact us