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

Last Comment

welcome 123

8/22/2022 - Mon

Ganapathi

5/4/2014

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

5/4/2014

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

5/4/2014

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

5/4/2014

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

5/4/2014

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

welcome 123

5/4/2014

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

5/4/2014

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

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

5/4/2014

ASKER

Thanks a lot this worked.