Link to home
Start Free TrialLog in
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/questions/28425882/How-to-update-Date-column-of-sysbase-IQ-database-Table.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/questions/28425882/How-to-update-Date-column-of-sysbase-IQ-database-Table.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
Avatar of Ganapathi
Ganapathi
Flag of India image

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?
Avatar of welcome 123
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
use SELECT instead of SET.

SELECT @mydate = '05/05/2014'
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
You need to execute both the variable assignment(SELECT) and UPDATE statements together.
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
ASKER CERTIFIED SOLUTION
Avatar of Ganapathi
Ganapathi
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot this worked.