Convert in psql

I created a sql statement in Microsoft sql server as
Update [dbo].[custeqip]
Set [yearofmfg] = convert(varchar(4), cast(‘01/01/‘ +right ( cast([yearofmfg] as int )’2) as datatime )’120) where [ yearofmfg] <> 0
It worked fine in ms sql server
I was trying to use the same statement in psql and it’s giving me an error like
The second parameter yearofmfg for convert is invalid
Can some one help me with this
Thanks
Susmitha SajjaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mirtheilSoftware DeveloperCommented:
Are you using PostgreSQL or Pervasive / Actian PSQL?
What exactly are you trying to accomplish?  What's the [yearofmfg] column defined as?  What are you expecting the result to be?
0
Susmitha SajjaAuthor Commented:
It's pervasive sql. Yearofmfg is a column with type smallint.
What was I was trying to do is that yearofmfg is the manufacture year of parts and some fields in it are 99 or 97 which should be 1999 or 1997 and some are 7 or 5 or 1 which should be 2007 or 2005 or 2001 I was trying to convert them. The above sql statement worked in ms sql
0
Bill BachPresident and Btrieve GuruCommented:
Try this instead:  IF(yearofmfg<50,yearofmfg+2000,yearofmfg+1900)
You can adjust the "50" in there to change your cut-over year.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Susmitha SajjaAuthor Commented:
But I don't want to change if it's 0.
0
Bill BachPresident and Btrieve GuruCommented:
IF(yearofmfg<50,IF(yearofmfg>0,yearofmfg+2000,0),yearofmfg+1900)

This version leaves a 0 at 0.
0
Susmitha SajjaAuthor Commented:
I'm getting a syntax error as if<< ?? >> yearofmfg < 50, if ( yearofmfg >0, yearofmfg + 2000, 0 ) , yearofmfg +1900)
0
Bill BachPresident and Btrieve GuruCommented:
Looks like you are missing a paren.  I've added spaces to make it easier to read:
   IF( yearofmfg < 50 , IF( yearofmfg > 0, yearofmfg + 2000 , 0 ) , yearofmfg + 1900 );

You an test with these three simple statements:
  select IF( 0 < 50 , IF( 0 > 0, 0 + 2000 , 0 ) , 0 + 1900 );
  select IF( 7 < 50 , IF( 7 > 0, 7 + 2000 , 0 ) , 7 + 1900 );
  select IF( 77 < 50 , IF( 77 > 0, 77 + 2000 , 0 ) , 77 + 1900 );
0
Susmitha SajjaAuthor Commented:
I'm still getting the same error
IF << ??? >> ( yearofmfg < 50 ......
0
Bill BachPresident and Btrieve GuruCommented:
What about when you run the other three statements?  Do those evaluate properly?
0
Susmitha SajjaAuthor Commented:
Omg it worked but the date has some fields that are 2007 now they are 4007! Is there anyway I can go back to the way it is or change the 4007 to 2007
0
Bill BachPresident and Btrieve GuruCommented:
Since you don't post your entire query, let me take a stab:

Update custeqip
Set yearofmfg = IF(yearofmfg<50,yearofmfg+2000,yearofmfg+1900)
WHERE yearofmfg <> 0

Note that with the WHERE clause involved, you don't need the extra IF, which is why I didn't include it the first time.

As with any update, you can test like this:
SELECT yearofmfg, IF(yearofmfg<50,yearofmfg+2000,yearofmfg+1900)
FROM custeqip
WHERE yearofmfg <> 0
0
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Hello,

I don't know exact difference between PostgreSQL and Pervasive. But it you are trying to do some this in PostgreSQL.

You can use below statement instead of "convert(varchar(4), cast(‘01/01/‘ +right ( cast([yearofmfg] as int )’2) as datatime )’120)".

EXTRAcT(YEAR FROM TO_DATE('01/01/'||'97', 'dd/MM/yy'));

Open in new window


Exact statement to just verify the sql is.

SELECT EXTRAcT(YEAR FROM TO_DATE('01/01/'||'97', 'dd/MM/yy'));

Open in new window


Hope it will help you.
0
Bill BachPresident and Btrieve GuruCommented:
This is why we test UPDATE queries with SELECT statements before running them.  Looks like you needed another WHERE clause to NOT change items not in the 1-100 range:
"   AND yearofmfg <100"

You can change the 4000's back to 2000's with this:
   Update custeqip
   Set yearofmfg = yearofmfg-2000)
   WHERE yearofmfg > 4000
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Susmitha SajjaAuthor Commented:
How to update for 1900
0
Bill BachPresident and Btrieve GuruCommented:
I don't understand your last question.  Please clarify.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.