CODEWITHSUNDEEP
sqladvantage-database-server
user8591026
user8591026

Reputation:

Update to use 1st of a given Month

I don't have any example SQL for this, but using Advantage Architect. I have one table where I have the year (eg 2017), an the Month (eg 04). I want to update another table (using an inner join on a field I have already identified, so don't worry about that bit) to be a date in the format of dd/mm/yyyy and for the day to be 01 for every date used, using the Month and Year from the other table as mentioned above.

My fields

Upvotes: 1

Views: 76

Answers (1)

Ken White
Ken White

Reputation: 125749

ADS provides somewhat limited date handling functionality. The easiest date format to use seems to be YYYY-MM-DD, but to convert from your columns to the date you're looking for gets somewhat convoluted.

Here's an example that should help you work things out.

create table #temp (calyear numeric(4, 0), calmonth numeric(2,0));
insert into #temp (calyear, calmonth) values (2017, 10);
insert into #temp (calyear, calmonth) values (2017, 7);

/* 
  Start convoluted workaround here. Cast the result of the concatenation below 
  (which creates a string in the format 'YYYY-MM-DD') into a date value
*/
select cast(txtdate as SQL_DATE) as newdate from 
(
  /* 
     Concatenate year, month (adding leading zero if needed to make two-digit)
     and month
  */
  select 
    trim(cast(calyear as SQL_Char)) + '-' + 
    right('00' + trim(cast(calmonth as SQL_Char)), 2) + 
    '-01' as txtdate from #temp
) t;

Upvotes: 1

Related Questions