• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 85
  • Last Modified:

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
0
Susmitha Sajja
Asked:
Susmitha Sajja
1 Solution
 
mirtheilCommented:
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 BachPresidentCommented:
Try this instead:  IF(yearofmfg<50,yearofmfg+2000,yearofmfg+1900)
You can adjust the "50" in there to change your cut-over year.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Susmitha SajjaAuthor Commented:
But I don't want to change if it's 0.
0
 
Bill BachPresidentCommented:
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 BachPresidentCommented:
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 BachPresidentCommented:
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 BachPresidentCommented:
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 BachPresidentCommented:
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
 
Susmitha SajjaAuthor Commented:
How to update for 1900
0
 
Bill BachPresidentCommented:
I don't understand your last question.  Please clarify.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now