How to convert a string (in three separate columns) to a timestamp(one column) MYSQL
Hi i'm a new coder and messed up on my sql table. Instead of storing my date with a timestamp I made the date in three separate columns: day, month, and year. I now realized that I need these in a timestamp. So I can perform more complicated queries.
Here is what I need the UPDATE to look like:
UPDATE coding_tracker SET coded_at = column(day)"/"column(month_number(month))"/"column(year);
Thank you in advance
mysql string-to-datetime
asked Nov 24 '18 at 0:11
Mason HorderMason Horder
64
add a comment |
Hi i'm a new coder and messed up on my sql table. Instead of storing my date with a timestamp I made the date in three separate columns: day, month, and year. I now realized that I need these in a timestamp. So I can perform more complicated queries.
Here is what I need the UPDATE to look like:
UPDATE coding_tracker SET coded_at = column(day)"/"column(month_number(month))"/"column(year);
Thank you in advance
mysql string-to-datetime
asked Nov 24 '18 at 0:11
Mason HorderMason Horder
64
I have been able to make this much so far UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT());
– Mason Horder
Nov 24 '18 at 0:14
1
Is your month column a string or a number? If a string, is it short or long month names? If a number, does it start with 0 or 1 for January?
– Nick
Nov 24 '18 at 0:44
While you're changing this, it's a good time to ask yourself if you are really 100% sure you need a timestamp. Timestamps have a limited range of dates - they cannot represent any year before 1970 nor after 2038. The alternative to timestamp with just a date in it is a "date" (any year from 0000 to 9999 goes) , or a datetime (can also contain the time, not just the date).
– user3277192
Nov 24 '18 at 0:59
my months are names that are capitled ex: November
– Mason Horder
Nov 24 '18 at 1:01
add a comment |
Hi i'm a new coder and messed up on my sql table. Instead of storing my date with a timestamp I made the date in three separate columns: day, month, and year. I now realized that I need these in a timestamp. So I can perform more complicated queries.
Here is what I need the UPDATE to look like:
UPDATE coding_tracker SET coded_at = column(day)"/"column(month_number(month))"/"column(year);
Thank you in advance
mysql string-to-datetime
asked Nov 24 '18 at 0:11
Mason HorderMason Horder
64
Hi i'm a new coder and messed up on my sql table. Instead of storing my date with a timestamp I made the date in three separate columns: day, month, and year. I now realized that I need these in a timestamp. So I can perform more complicated queries.
Here is what I need the UPDATE to look like:
UPDATE coding_tracker SET coded_at = column(day)"/"column(month_number(month))"/"column(year);
Thank you in advance
mysql string-to-datetime
mysql string-to-datetime
asked Nov 24 '18 at 0:11
Mason HorderMason Horder
64
asked Nov 24 '18 at 0:11
Mason HorderMason Horder
64
edited Nov 24 '18 at 15:17
Mason Horder
asked Nov 24 '18 at 0:11
Mason HorderMason Horder
64
asked Nov 24 '18 at 0:11
Mason HorderMason Horder
64
asked Nov 24 '18 at 0:11
Mason HorderMason Horder
64
64
I have been able to make this much so far UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT());
– Mason Horder
Nov 24 '18 at 0:14
1
Is your month column a string or a number? If a string, is it short or long month names? If a number, does it start with 0 or 1 for January?
– Nick
Nov 24 '18 at 0:44
While you're changing this, it's a good time to ask yourself if you are really 100% sure you need a timestamp. Timestamps have a limited range of dates - they cannot represent any year before 1970 nor after 2038. The alternative to timestamp with just a date in it is a "date" (any year from 0000 to 9999 goes) , or a datetime (can also contain the time, not just the date).
– user3277192
Nov 24 '18 at 0:59
my months are names that are capitled ex: November
– Mason Horder
Nov 24 '18 at 1:01
add a comment |
I have been able to make this much so far UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT());
– Mason Horder
Nov 24 '18 at 0:14
1
Is your month column a string or a number? If a string, is it short or long month names? If a number, does it start with 0 or 1 for January?
– Nick
Nov 24 '18 at 0:44
While you're changing this, it's a good time to ask yourself if you are really 100% sure you need a timestamp. Timestamps have a limited range of dates - they cannot represent any year before 1970 nor after 2038. The alternative to timestamp with just a date in it is a "date" (any year from 0000 to 9999 goes) , or a datetime (can also contain the time, not just the date).
– user3277192
Nov 24 '18 at 0:59
my months are names that are capitled ex: November
– Mason Horder
Nov 24 '18 at 1:01
I have been able to make this much so far UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT());
– Mason Horder
Nov 24 '18 at 0:14
I have been able to make this much so far UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT());
– Mason Horder
Nov 24 '18 at 0:14
1
1
Is your month column a string or a number? If a string, is it short or long month names? If a number, does it start with 0 or 1 for January?
– Nick
Nov 24 '18 at 0:44
Is your month column a string or a number? If a string, is it short or long month names? If a number, does it start with 0 or 1 for January?
– Nick
Nov 24 '18 at 0:44
While you're changing this, it's a good time to ask yourself if you are really 100% sure you need a timestamp. Timestamps have a limited range of dates - they cannot represent any year before 1970 nor after 2038. The alternative to timestamp with just a date in it is a "date" (any year from 0000 to 9999 goes) , or a datetime (can also contain the time, not just the date).
– user3277192
Nov 24 '18 at 0:59
While you're changing this, it's a good time to ask yourself if you are really 100% sure you need a timestamp. Timestamps have a limited range of dates - they cannot represent any year before 1970 nor after 2038. The alternative to timestamp with just a date in it is a "date" (any year from 0000 to 9999 goes) , or a datetime (can also contain the time, not just the date).
– user3277192
Nov 24 '18 at 0:59
my months are names that are capitled ex: November
– Mason Horder
Nov 24 '18 at 1:01
my months are names that are capitled ex: November
– Mason Horder
Nov 24 '18 at 1:01
add a comment |
1 Answer
1
active
oldest
votes
Assuming your columns are called day, month_number and year, this query should work:
UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month_number, year), '%d/%m/%Y')
In the case where your month column is a name, you can change %m in the above query to %b for short month names (Jan..Dec) or %M for long month names (January..December) e.g. for long names:
UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month, year), '%d/%M/%Y')
Documentation about formats for STR_TO_DATE can be found in the DATE_FORMAT section of the MySQL manual.
answered Nov 24 '18 at 0:49
NickNick
26.6k111839
thank you, it works except i need my months to be converted from a string to a number
– Mason Horder
Nov 24 '18 at 1:02
Are your months short or long names?
– Nick
Nov 24 '18 at 1:03
they are full length, and the first letter is capital
– Mason Horder
Nov 24 '18 at 1:05
@MasonHorder see my edit
– Nick
Nov 24 '18 at 1:09
i am getting this error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
– Mason Horder
Nov 24 '18 at 1:12
|
show 3 more comments
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53454111%2fhow-to-convert-a-string-in-three-separate-columns-to-a-timestampone-column-m%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Assuming your columns are called day, month_number and year, this query should work:
UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month_number, year), '%d/%m/%Y')
In the case where your month column is a name, you can change %m in the above query to %b for short month names (Jan..Dec) or %M for long month names (January..December) e.g. for long names:
UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month, year), '%d/%M/%Y')
Documentation about formats for STR_TO_DATE can be found in the DATE_FORMAT section of the MySQL manual.
answered Nov 24 '18 at 0:49
NickNick
26.6k111839
thank you, it works except i need my months to be converted from a string to a number
– Mason Horder
Nov 24 '18 at 1:02
Are your months short or long names?
– Nick
Nov 24 '18 at 1:03
they are full length, and the first letter is capital
– Mason Horder
Nov 24 '18 at 1:05
@MasonHorder see my edit
– Nick
Nov 24 '18 at 1:09
i am getting this error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
– Mason Horder
Nov 24 '18 at 1:12
|
show 3 more comments
Assuming your columns are called day, month_number and year, this query should work:
UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month_number, year), '%d/%m/%Y')
In the case where your month column is a name, you can change %m in the above query to %b for short month names (Jan..Dec) or %M for long month names (January..December) e.g. for long names:
UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month, year), '%d/%M/%Y')
Documentation about formats for STR_TO_DATE can be found in the DATE_FORMAT section of the MySQL manual.
answered Nov 24 '18 at 0:49
NickNick
26.6k111839
thank you, it works except i need my months to be converted from a string to a number
– Mason Horder
Nov 24 '18 at 1:02
Are your months short or long names?
– Nick
Nov 24 '18 at 1:03
they are full length, and the first letter is capital
– Mason Horder
Nov 24 '18 at 1:05
@MasonHorder see my edit
– Nick
Nov 24 '18 at 1:09
i am getting this error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
– Mason Horder
Nov 24 '18 at 1:12
|
show 3 more comments
Assuming your columns are called day, month_number and year, this query should work:
UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month_number, year), '%d/%m/%Y')
In the case where your month column is a name, you can change %m in the above query to %b for short month names (Jan..Dec) or %M for long month names (January..December) e.g. for long names:
UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month, year), '%d/%M/%Y')
Documentation about formats for STR_TO_DATE can be found in the DATE_FORMAT section of the MySQL manual.
answered Nov 24 '18 at 0:49
NickNick
26.6k111839
Assuming your columns are called day, month_number and year, this query should work:
UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month_number, year), '%d/%m/%Y')
In the case where your month column is a name, you can change %m in the above query to %b for short month names (Jan..Dec) or %M for long month names (January..December) e.g. for long names:
UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month, year), '%d/%M/%Y')
Documentation about formats for STR_TO_DATE can be found in the DATE_FORMAT section of the MySQL manual.
answered Nov 24 '18 at 0:49
NickNick
26.6k111839
edited Nov 24 '18 at 1:14
answered Nov 24 '18 at 0:49
NickNick
26.6k111839
answered Nov 24 '18 at 0:49
NickNick
26.6k111839
answered Nov 24 '18 at 0:49
NickNick
26.6k111839
26.6k111839
thank you, it works except i need my months to be converted from a string to a number
– Mason Horder
Nov 24 '18 at 1:02
Are your months short or long names?
– Nick
Nov 24 '18 at 1:03
they are full length, and the first letter is capital
– Mason Horder
Nov 24 '18 at 1:05
@MasonHorder see my edit
– Nick
Nov 24 '18 at 1:09
i am getting this error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
– Mason Horder
Nov 24 '18 at 1:12
|
show 3 more comments
thank you, it works except i need my months to be converted from a string to a number
– Mason Horder
Nov 24 '18 at 1:02
Are your months short or long names?
– Nick
Nov 24 '18 at 1:03
they are full length, and the first letter is capital
– Mason Horder
Nov 24 '18 at 1:05
@MasonHorder see my edit
– Nick
Nov 24 '18 at 1:09
i am getting this error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
– Mason Horder
Nov 24 '18 at 1:12
thank you, it works except i need my months to be converted from a string to a number
– Mason Horder
Nov 24 '18 at 1:02
thank you, it works except i need my months to be converted from a string to a number
– Mason Horder
Nov 24 '18 at 1:02
Are your months short or long names?
– Nick
Nov 24 '18 at 1:03
Are your months short or long names?
– Nick
Nov 24 '18 at 1:03
they are full length, and the first letter is capital
– Mason Horder
Nov 24 '18 at 1:05
they are full length, and the first letter is capital
– Mason Horder
Nov 24 '18 at 1:05
@MasonHorder see my edit
– Nick
Nov 24 '18 at 1:09
@MasonHorder see my edit
– Nick
Nov 24 '18 at 1:09
i am getting this error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
– Mason Horder
Nov 24 '18 at 1:12
i am getting this error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
– Mason Horder
Nov 24 '18 at 1:12
|
show 3 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53454111%2fhow-to-convert-a-string-in-three-separate-columns-to-a-timestampone-column-m%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I have been able to make this much so far UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT());
– Mason Horder
Nov 24 '18 at 0:14
1
Is your month column a string or a number? If a string, is it short or long month names? If a number, does it start with 0 or 1 for January?
– Nick
Nov 24 '18 at 0:44
While you're changing this, it's a good time to ask yourself if you are really 100% sure you need a timestamp. Timestamps have a limited range of dates - they cannot represent any year before 1970 nor after 2038. The alternative to timestamp with just a date in it is a "date" (any year from 0000 to 9999 goes) , or a datetime (can also contain the time, not just the date).
– user3277192
Nov 24 '18 at 0:59
my months are names that are capitled ex: November
– Mason Horder
Nov 24 '18 at 1:01