Avatar of welcome 123
welcome 123
 asked on

How to update Date column of sysbase IQ database Table

Hi,

This question I asked in this page earlier,
https://www.experts-exchange.com/OS/Unix/Q_28425882.html


I have Sybase iq Table Name dates_col in that I have column name DATE_BD whose
data stucture:
column_name: DATE_BD4
domain_name:date
width:4
scale:0
nulls:N

update  temp_table
set DATE_BD4= getdate()
WHERE PROCESS_MONTH        = 'MAR'
AND   PROCESS_YEAR        = 2014;
This works and updates DATE_BD4 with current date.

But I am only able to set to current date today's date to that column, is there is any way I can update any other value like tomorrow's date for that
column like below.

update  temp_table
set dateformat(DATE_BD4,'MM/DD/YYYY') = '05/05/2014'
WHERE PROCESS_MONTH        = 'MAR'
AND   PROCESS_YEAR        = 2014;
commit;

But I am getting the following error
> Script lines: 1-5 --------------------------
 SQL Anywhere Error -131: Syntax error near '=' on line 2
 Msg: 102, Level: 15, State: 0
 Line: 0

How can I update other dates by giving the value to the column directly.
https://www.experts-exchange.com/OS/Unix/Q_28425882.html


I want to assign any date to it, what I mean is here

update  temp_table
DATE_BD4 = '05/05/2014'
WHERE PROCESS_MONTH        = 'MAR'
AND   PROCESS_YEAR        = 2014;
commit;

DATE_BD4  can be,
DATE_BD4 = '05/05/2014'
DATE_BD4 = '10/07/2001'
DATE_BD4 = '03/05/1999'
DATE_BD4 = '09/07/2017'
DATE_BD4 = '08/25/2014'

etc you are functions might not happen in this case, I want the correct syntax where I can execute the update statement to set date column for any date value.

I am doing testing for this table, I will re-assign values as and when my testing is complete.

Thanks,
Sailaja
Sybase DatabaseOracle DatabaseDB2

Avatar of undefined
Last Comment
welcome 123

8/22/2022 - Mon
Ganapathi

Your below query will not work as the 2nd has a syntax error. You cannot use like that.

update  temp_table
set dateformat(DATE_BD4,'MM/DD/YYYY') = '05/05/2014'
WHERE PROCESS_MONTH        = 'MAR'
AND   PROCESS_YEAR        = 2014;

You can create a variable and pass it to the UPDATE statement, like below.

declare @mydate DATETIME
SET @mydate = '05/05/2014'

update  temp_table
DATE_BD4 = @mydate
WHERE PROCESS_MONTH        = 'MAR'
AND   PROCESS_YEAR        = 2014;

Open in new window

Is that what you are looking for?
welcome 123

ASKER
For this query the ouptu Error
update  temp_table
set dateformat(DATE_BD4,'MM/DD/YYYY') = '05/05/2014'
WHERE PROCESS_MONTH        = 'MAR'
AND   PROCESS_YEAR        = 2014;


> Script lines: 1-4 --------------------------
 SQL Anywhere Error -131: Syntax error near '=' on line 2
 Msg: 102, Level: 15, State: 0
 Line: 0

For second declare worked but SET didn't work.

SET @mydate = '05/05/2014'


> Script lines: 1-1 --------------------------
 SQL Anywhere Error -260: Variable '@mydate' not found
 Msg: 137, Level: 15, State: 0
 Line: 0

Thanks,
Sailaja
Ganapathi

use SELECT instead of SET.

SELECT @mydate = '05/05/2014'
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
welcome 123

ASKER
declare @mydate DATETIME
 Command was executed successfully

SELECT @mydate = '05/05/2014'

> Script lines: 1-1 --------------------------
 SQL Anywhere Error -260: Variable '@mydate' not found
 Msg: 137, Level: 15, State: 0
 Line: 0
Ganapathi

You need to execute both the variable assignment(SELECT) and UPDATE statements together.
welcome 123

ASKER
update  temp_table
set dateformat(DATE_BD4,'MM/DD/YYYY') = SELECT @mydate = '05/05/2014'
WHERE PROCESS_MONTH        = 'MAR'
AND   PROCESS_YEAR        = 2014;

> Script lines: 1-7 --------------------------
 SQL Anywhere Error -131: Syntax error near '=' on line 2
 Msg: 102, Level: 15, State: 0
 Line: 0

Please give the full update and select combination.

Thanks,
Sailaja
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Ganapathi

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
welcome 123

ASKER
Thanks a lot this worked.