-4

I need some help in converting or equivalent of below in postgreSQL..

From MSSQL code to postgreSQL:-

  1. CONVERT(bit,CASE WHEN column_a & 1 > 0 THEN 1 ELSE 0 END) as ABC

  2. CONVERT(tinyint,CASE WHEN column_a IS NOT NULL THEN 0 END) as ABC

  3. CONVERT(smallint,CASE WHEN col_year = (SELECT year(col_date) FROM tab A WHERE A.a = 192) THEN 1 ELSE 0 END) as ABC

  4. TO_CHAR(TO_CHAR(YEAR(Date))||'Y') as ABC

  5. CASE WHEN COL_A = 1 THEN TO_CHAR(YEAR(Date))||'Y'

  6. WHEN COL_A = 4 THEN TO_CHAR (YEAR(Date) ) || 'Q' || TO_CHAR(Month(Date)/3 ) ELSE NULL END as ABC

Please help..

Improve this question
10
  • 2
    Please explain what each statement is doing. You can't assume that those that know Postgres also know SQL Server. But TO_CHAR(TO_CHAR(YEAR(Date))||'Y') doesn't look at all like T-SQL Commented Jun 21, 2021 at 17:15
  • 2
    Unrelated to your problem, but: Postgres 9.5 is no longer supported you should plan an upgrade as soon as possible. Commented Jun 21, 2021 at 17:16
  • @a_horse_with_no_name sure..these are all conversion statements in sql to other data types based on case statements basis..just looking for equivalent postgres ones..replacements as there is no to_char(year) in postgres nor convert..thanks Commented Jun 21, 2021 at 17:59
  • Well "conversion into another SQL type" is done with cast() in Postgres. Commented Jun 21, 2021 at 18:27
  • 1
    For one: there is no to_char() in SQL Server. And TO_CHAR(TO_CHAR(YEAR(Date))||'Y') makes no sense to begin with and SQL Sever doesn't use the standard || concatenation operator - so that can't be your SQL Server code. But if Date is a column with the data type date then I guess(!) you might be looking for to_char("date", 'YYYY"Y"') in Postgres. Commented Jun 22, 2021 at 5:40

1 Answer 1

Reset to default
0

I have no idea what the convert() parts are supposed to do, but I am trying to guess(!) what the intention to_char() (that can't be valid SQL Server code) is trying to achieve.

As documented in the manual to_char() needs two arguments: the value to be formatted (e.g. a date or timestamp value) and the "format mask".

To extract the year as a string from a date (or timestamp) value and append the letter Y use:

to_char("date", 'YYYY"Y"')

e.g. to_char(current_date, 'YYYY"Y"') returns '2021Y'. Alternatively use `to_char("date", 'YYYY')||'Y'

The format mask to get the quarter is 'Q' and 'MM' for the month. So to get a string with a date's quarter use to_char("date", 'Q').

To extract the month as a number, use the extract() function, e.g. extract(month from some_date) / 3

To extract the year use extract(year from some_date)

CONVERT(tinyint,CASE WHEN column_a IS NOT NULL THEN 0 END) looks like you just want a flag if the column is null. This can be e.g. written as column_a is null which will return a boolean value with true or false (slightly different than you original expression that returned null if the column is null). If you really prefer numbers instead of booleans use case when column_a is not null then 0 end. I don't exactly know what that convert() function is doing, but you really don't like booleans or integers to be used as a flag, then maybe you want to cast that to a smallint: cast(case when column_a is not null then 0 end as smallint)

Improve this answer
3
  • thanks very much...that helped a lot..just fixing this below from what you suggested...CONVERT(scovert (smallint,CASE WHEN colyear= (SELECT year(coldate) FROM table x1_0 WHERE x1_0.colDate1 = 100) THEN 1 ELSE 0 END) as ABC, IN PG...It is like..anything wrong here.. cast(case extract(coldate) when x0_0.colyear) as SMALLINT FROM table x1_0 WHERE x1_0.colDate1 = 100) THEN 1 ELSE 0 END) as ABC, Commented Jun 22, 2021 at 7:01
  • As documented in the manual extract() needs two parameters and the from keyword is mandatory. So obviously extract(coldate) is wrong - it must be extract(year from coldate). Please do read the manual - the correct syntax for all functions I mentioned is documented there including all allowed values for the parameters. Commented Jun 22, 2021 at 7:12
  • thanks again...sure..somehow this isnt working for me..let me check the manual...its going above my head currently.. CAST(CASE extract(year from coldate) When x0_0.colyear) as SMALLINT FROM tbl.Cola x1_0 WHERE x1_0.colddate = 192) THEN 1 ELSE 0 END) as ABC, Commented Jun 22, 2021 at 7:22

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.