Styjun

Non-visual Computers - thoughts?

Sun setting in East!

Cross-referencing enumerate item

Please help me identify the bold slashes between staves

Would it be possible to have a GMO that produces chocolate?

Which household object drew this pattern?

Shouldn't the "credit score" prevent Americans from going deeper and deeper into personal debt?

How to respectfully refuse to assist co-workers with IT issues?

Why did MS-DOS applications built using Turbo Pascal fail to start with a division by zero error on faster systems?

In the MCU, why does Mjölnir retain its enchantments after Ragnarok?

Cultural before-and-afters

Why in most German places is the church the tallest building?

Does travel insurance for short flight delays exist?

Architectural feasibility of a tiered circular stone keep

Why did this happen to Thanos's ships at the end of "Avengers: Endgame"?

Are there any music source codes for sound chips?

What magic extends life or grants immortality?

Science fiction short story where aliens contact a drunk about Earth's impending destruction

What to say to a student who has failed?

Notepad++ - How to find multiple values on the same line in any permutation

Are modern clipless shoes and pedals that much better than toe clips and straps?

How can I unambiguously ask for a new user's "Display Name"?

Numbers Decrease while Letters Increase

Irish Snap: Variant Rules

How to combine integer columns to form date in Oracle SQL

How to generate an entity-relationship (ER) diagram using Oracle SQL DeveloperOracle SQL - select first result only if column X equals YIssues with importing data in date format from excel to oracle 10g?sql oracle ignore holidaysDate column in oracleCombining Same Oracle SQL Scripts in One?How do you take a date in one format as input and output a date in a different format in Oracle SQL?Oracle SQL - Importing Dates from CSV (Excel Format)How to get difference between numbers with same dates in oracle?How to avoid zero value in column after minus 2 dates in sql?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have a table with columns salDay,SalYear and Salmonth.saLday stores intergers between 1 and 31 , salYear stores integer years e.g 2013,2015... and salMonth stores interger months between 1 and 12.I want to populate another column called received_date by combining salday,salmonth and salyear to get a date in the form of DD/MM/YYYY.

any ideas please ?

oracle-sqldeveloper

share|improve this question

asked Mar 27 at 16:35

Adam MkiniAdam Mkini

111 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why are you storing it split up in the first place?
  
  – Alex Poole
  Mar 27 at 16:59
  

  
  
  
  

add a comment | 

0

I have a table with columns salDay,SalYear and Salmonth.saLday stores intergers between 1 and 31 , salYear stores integer years e.g 2013,2015... and salMonth stores interger months between 1 and 12.I want to populate another column called received_date by combining salday,salmonth and salyear to get a date in the form of DD/MM/YYYY.

any ideas please ?

oracle-sqldeveloper

share|improve this question

asked Mar 27 at 16:35

Adam MkiniAdam Mkini

111 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why are you storing it split up in the first place?
  
  – Alex Poole
  Mar 27 at 16:59
  

  
  
  
  

add a comment | 

I have a table with columns salDay,SalYear and Salmonth.saLday stores intergers between 1 and 31 , salYear stores integer years e.g 2013,2015... and salMonth stores interger months between 1 and 12.I want to populate another column called received_date by combining salday,salmonth and salyear to get a date in the form of DD/MM/YYYY.

any ideas please ?

oracle-sqldeveloper

share|improve this question

asked Mar 27 at 16:35

Adam MkiniAdam Mkini

111 bronze badge

share|improve this question

asked Mar 27 at 16:35

Adam MkiniAdam Mkini

111 bronze badge

share|improve this question

asked Mar 27 at 16:35

Adam MkiniAdam Mkini

111 bronze badge

asked Mar 27 at 16:35

Adam MkiniAdam Mkini

111 bronze badge

asked Mar 27 at 16:35

Adam MkiniAdam Mkini

111 bronze badge

1 Answer
1

active

oldest

votes

1

You could just concatenate the columns (explicitly or implcitly converted to strings) together and use to_date() to convert the result to an actual date:

create table your_table (salDay number, salMonth number, salYear number);
insert into your_table (salDay, salMonth, salYear) values (27, 3, 2019);

select to_date(salYear ||'-'|| salMonth ||'-'|| salDay, 'YYYY-MM-DD') as salDate
from your_table;

SALDATE 
-------------------
2019-03-27 00:00:00

If you want this as another column in your table then you can use a virtual column with the same conversion:

alter table your_table
add salDate date generated always as (to_date(salYear ||'-'|| salMonth ||'-'|| salDay, 'YYYY-MM-DD'));

Table YOUR_TABLE altered.

select * from your_table;

 SALDAY SALMONTH SALYEAR SALDATE 
---------- ---------- ---------- -------------------
 27 3 2019 2019-03-27 00:00:00

Of course, both of these will have problems if you have an invalid date. In the first case it'll error when you use that query; in the second it'll error when you query the new column. But it won't stop you putting bad data in still.

insert into your_table (salDay, salMonth, salYear) values (29, 2, 2019);

1 row inserted.

select * from your_table;

ORA-01839: date not valid for month specified

You could use an index or constraint on that virtual column to prevent that though - anything that will cause the conversion to be attempted as part of the insert, which in turn will cause the insert to fail:

create index your_index on your_table (salDate);

Index YOUR_INDEX created.

insert into your_table (salDay, salMonth, salYear) values (29, 2, 2019);

ORA-01839: date not valid for month specified

share|improve this answer

edited Mar 27 at 17:13

answered Mar 27 at 17:07

Alex PooleAlex Poole

141k77 gold badges117117 silver badges198198 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55382288%2fhow-to-combine-integer-columns-to-form-date-in-oracle-sql%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

You could just concatenate the columns (explicitly or implcitly converted to strings) together and use to_date() to convert the result to an actual date:

create table your_table (salDay number, salMonth number, salYear number);
insert into your_table (salDay, salMonth, salYear) values (27, 3, 2019);

select to_date(salYear ||'-'|| salMonth ||'-'|| salDay, 'YYYY-MM-DD') as salDate
from your_table;

SALDATE 
-------------------
2019-03-27 00:00:00

If you want this as another column in your table then you can use a virtual column with the same conversion:

alter table your_table
add salDate date generated always as (to_date(salYear ||'-'|| salMonth ||'-'|| salDay, 'YYYY-MM-DD'));

Table YOUR_TABLE altered.

select * from your_table;

 SALDAY SALMONTH SALYEAR SALDATE 
---------- ---------- ---------- -------------------
 27 3 2019 2019-03-27 00:00:00

Of course, both of these will have problems if you have an invalid date. In the first case it'll error when you use that query; in the second it'll error when you query the new column. But it won't stop you putting bad data in still.

insert into your_table (salDay, salMonth, salYear) values (29, 2, 2019);

1 row inserted.

select * from your_table;

ORA-01839: date not valid for month specified

You could use an index or constraint on that virtual column to prevent that though - anything that will cause the conversion to be attempted as part of the insert, which in turn will cause the insert to fail:

create index your_index on your_table (salDate);

Index YOUR_INDEX created.

insert into your_table (salDay, salMonth, salYear) values (29, 2, 2019);

ORA-01839: date not valid for month specified

share|improve this answer

edited Mar 27 at 17:13

answered Mar 27 at 17:07

Alex PooleAlex Poole

141k77 gold badges117117 silver badges198198 bronze badges

add a comment | 

1

You could just concatenate the columns (explicitly or implcitly converted to strings) together and use to_date() to convert the result to an actual date:

create table your_table (salDay number, salMonth number, salYear number);
insert into your_table (salDay, salMonth, salYear) values (27, 3, 2019);

select to_date(salYear ||'-'|| salMonth ||'-'|| salDay, 'YYYY-MM-DD') as salDate
from your_table;

SALDATE 
-------------------
2019-03-27 00:00:00

If you want this as another column in your table then you can use a virtual column with the same conversion:

alter table your_table
add salDate date generated always as (to_date(salYear ||'-'|| salMonth ||'-'|| salDay, 'YYYY-MM-DD'));

Table YOUR_TABLE altered.

select * from your_table;

 SALDAY SALMONTH SALYEAR SALDATE 
---------- ---------- ---------- -------------------
 27 3 2019 2019-03-27 00:00:00

Of course, both of these will have problems if you have an invalid date. In the first case it'll error when you use that query; in the second it'll error when you query the new column. But it won't stop you putting bad data in still.

insert into your_table (salDay, salMonth, salYear) values (29, 2, 2019);

1 row inserted.

select * from your_table;

ORA-01839: date not valid for month specified

You could use an index or constraint on that virtual column to prevent that though - anything that will cause the conversion to be attempted as part of the insert, which in turn will cause the insert to fail:

create index your_index on your_table (salDate);

Index YOUR_INDEX created.

insert into your_table (salDay, salMonth, salYear) values (29, 2, 2019);

ORA-01839: date not valid for month specified

share|improve this answer

edited Mar 27 at 17:13

answered Mar 27 at 17:07

Alex PooleAlex Poole

141k77 gold badges117117 silver badges198198 bronze badges

add a comment | 

You could just concatenate the columns (explicitly or implcitly converted to strings) together and use to_date() to convert the result to an actual date:

create table your_table (salDay number, salMonth number, salYear number);
insert into your_table (salDay, salMonth, salYear) values (27, 3, 2019);

select to_date(salYear ||'-'|| salMonth ||'-'|| salDay, 'YYYY-MM-DD') as salDate
from your_table;

SALDATE 
-------------------
2019-03-27 00:00:00

If you want this as another column in your table then you can use a virtual column with the same conversion:

alter table your_table
add salDate date generated always as (to_date(salYear ||'-'|| salMonth ||'-'|| salDay, 'YYYY-MM-DD'));

Table YOUR_TABLE altered.

select * from your_table;

 SALDAY SALMONTH SALYEAR SALDATE 
---------- ---------- ---------- -------------------
 27 3 2019 2019-03-27 00:00:00

Of course, both of these will have problems if you have an invalid date. In the first case it'll error when you use that query; in the second it'll error when you query the new column. But it won't stop you putting bad data in still.

insert into your_table (salDay, salMonth, salYear) values (29, 2, 2019);

1 row inserted.

select * from your_table;

ORA-01839: date not valid for month specified

You could use an index or constraint on that virtual column to prevent that though - anything that will cause the conversion to be attempted as part of the insert, which in turn will cause the insert to fail:

create index your_index on your_table (salDate);

Index YOUR_INDEX created.

insert into your_table (salDay, salMonth, salYear) values (29, 2, 2019);

ORA-01839: date not valid for month specified

share|improve this answer

edited Mar 27 at 17:13

answered Mar 27 at 17:07

Alex PooleAlex Poole

141k77 gold badges117117 silver badges198198 bronze badges

create table your_table (salDay number, salMonth number, salYear number);
insert into your_table (salDay, salMonth, salYear) values (27, 3, 2019);

select to_date(salYear ||'-'|| salMonth ||'-'|| salDay, 'YYYY-MM-DD') as salDate
from your_table;

SALDATE 
-------------------
2019-03-27 00:00:00

alter table your_table
add salDate date generated always as (to_date(salYear ||'-'|| salMonth ||'-'|| salDay, 'YYYY-MM-DD'));

Table YOUR_TABLE altered.

select * from your_table;

 SALDAY SALMONTH SALYEAR SALDATE 
---------- ---------- ---------- -------------------
 27 3 2019 2019-03-27 00:00:00

insert into your_table (salDay, salMonth, salYear) values (29, 2, 2019);

1 row inserted.

select * from your_table;

ORA-01839: date not valid for month specified

create index your_index on your_table (salDate);

Index YOUR_INDEX created.

insert into your_table (salDay, salMonth, salYear) values (29, 2, 2019);

ORA-01839: date not valid for month specified

share|improve this answer

edited Mar 27 at 17:13

answered Mar 27 at 17:07

Alex PooleAlex Poole

141k77 gold badges117117 silver badges198198 bronze badges

answered Mar 27 at 17:07

Alex PooleAlex Poole

141k77 gold badges117117 silver badges198198 bronze badges

answered Mar 27 at 17:07

Alex PooleAlex Poole

141k77 gold badges117117 silver badges198198 bronze badges