How to convert a string (in three separate columns) to a timestamp(one column) MYSQL












0















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







share|improve this question

























  • 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
















0















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







share|improve this question

























  • 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














0












0








0


1






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







share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 24 '18 at 15:17







Mason Horder

















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



















  • 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












1 Answer
1






active

oldest

votes


















0














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.






share|improve this answer


























  • 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













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


















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









0














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.






share|improve this answer


























  • 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


















0














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.






share|improve this answer


























  • 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
















0












0








0







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.






share|improve this answer















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.







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 24 '18 at 1:14

























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





















  • 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




















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







-

Popular posts from this blog

Berounka

Image
Berounka.mw-parser-output .entete.map{background-image:url("//upload.wikimedia.org/wikipedia/commons/7/7a/Picto_infobox_map.png")} Caractéristiques Longueur 139  km Bassin 8 861  km 2 Bassin collecteur Elbe Débit moyen ? Cours Source Confluence de la Mže et de la Radbuza · Localisation Pilsen · Altitude 305  m · Coordonnées 49° 45′ 13″ N, 13° 23′ 24″ E Confluence Vltava · Localisation Prague · Altitude 187  m · Coordonnées 49° 59′ 43″ N, 14° 24′ 04″ E Géographie Pays traversés   République tchèque modifier   La Berounka est une rivière de la République tchèque. Elle porte le nom de Mže depuis sa source en Allemagne près de la frontière jusqu'à sa confluence avec la Radbuza à Plzeň. Elle continue ensuite sous le nom de Berounka jusqu'à sa confluence avec la Vltava, près de Prague. La rivière est une destination prisée pour les amateurs de canoë, qui appréc...
Read more

Sphinx de Gizeh

Image
Pour les articles homonymes, voir Sphinx. Sphinx de Gizeh.mw-parser-output .entete.egypte-antique{background-image:url("//upload.wikimedia.org/wikipedia/commons/c/cc/Picto_infobox_ancient_Egypt.png")} Site d'Égypte antique Le visage du sphinx de Gizeh. Noms en arabe أبو الهول , Abou al-Hôl Localisation Région Basse-Égypte Coordonnées 29° 58′ 31″ nord, 31° 08′ 16″ est Géolocalisation sur la carte : Égypte Sphinx de Gizeh modifier   Le sphinx de Gizeh est la statue thérianthrope qui se dresse devant les grandes pyramides du plateau de Gizeh, en Basse-Égypte. Sculpture monumentale monolithique la plus grande du monde [ 1 ] avec 73,5 mètres de longueur, 14 mètres de largeur et 20,22 mètres de hauteur, elle représente un sphinx couchant. Réalisée vers -2500, elle serait attribuée à Djédefrê, l'un des pharaons de la IV e  dynastie, représentant son père le pharaon Khéops. Sommaire 1 Étymologie 2 Desc...
Read more

Different font size/position of beamer's navigation symbols template's content depending on regular/plain...

Image
3 Let's suppose I'd like to divert the navigation symbols template (e.g. in order replace them by the frame numbers, see this bug report ;) I would be facing troubles regarding the font size/position, depending on regular/plain frames, as shown by the following MCE. How could I get rid of these troubles? documentclass{beamer} usetheme{PaloAlto} setbeamertemplate{navigation symbols}{insertframenumber/inserttotalframenumber} begin{document} begin{frame} frametitle{A regular frame} end{frame} begin{frame}[plain] frametitle{A plain frame} end{frame} end{document} beamer header-footer share | improve this question asked Dec 10 '18 a...
Read more