formatting specific date to required format in notepad++
Good day everyone,
I have a data set in a csv file that has the following date formatting:
Tue Jun 16 18:01:56 BST 2009
Thu Aug 20 09:40:51 BST 2015
I am working on a MySQL database where the data needs to be uploaded into a DATETIME column thus the required format is presented below. The timezone abbreviations need to be removed completely, I am able to do that with find/replace as well as day abbreviations. So only the month abbreviations need to be converted to numbers and the year placed in the beginning:
YYYY-MM-DD HH:MM:SS
I know I can use CTRL+F to find and replace specific values in NP++. But as you might've guessed the date combinations in the file can be different. I have researched that it can be accomplished with a regular expression but I am not familiar with that at all. Are there any expert users who can advise how I could write the necessary expression to format and replace my dates? Thank you in advance.
mysql sql regex datetime notepad++
edited Nov 20 '18 at 10:10
Salman A
182k66339433
asked Nov 19 '18 at 20:00
asleniovasasleniovas
215
add a comment |
Good day everyone,
I have a data set in a csv file that has the following date formatting:
Tue Jun 16 18:01:56 BST 2009
Thu Aug 20 09:40:51 BST 2015
I am working on a MySQL database where the data needs to be uploaded into a DATETIME column thus the required format is presented below. The timezone abbreviations need to be removed completely, I am able to do that with find/replace as well as day abbreviations. So only the month abbreviations need to be converted to numbers and the year placed in the beginning:
YYYY-MM-DD HH:MM:SS
I know I can use CTRL+F to find and replace specific values in NP++. But as you might've guessed the date combinations in the file can be different. I have researched that it can be accomplished with a regular expression but I am not familiar with that at all. Are there any expert users who can advise how I could write the necessary expression to format and replace my dates? Thank you in advance.
mysql sql regex datetime notepad++
edited Nov 20 '18 at 10:10
Salman A
182k66339433
asked Nov 19 '18 at 20:00
asleniovasasleniovas
215
MySQL's LOAD DATA INFILE. has ways to parse out CVS files and data within them using MySQL user variables.. Something like this should workLOAD DATA INFILE .... (<columns> @datetime) SET <columns>, @datetime = STR_TO_DATE(..., LEFT(...))
– Raymond Nijland
Nov 19 '18 at 20:06
add a comment |
Good day everyone,
I have a data set in a csv file that has the following date formatting:
Tue Jun 16 18:01:56 BST 2009
Thu Aug 20 09:40:51 BST 2015
I am working on a MySQL database where the data needs to be uploaded into a DATETIME column thus the required format is presented below. The timezone abbreviations need to be removed completely, I am able to do that with find/replace as well as day abbreviations. So only the month abbreviations need to be converted to numbers and the year placed in the beginning:
YYYY-MM-DD HH:MM:SS
I know I can use CTRL+F to find and replace specific values in NP++. But as you might've guessed the date combinations in the file can be different. I have researched that it can be accomplished with a regular expression but I am not familiar with that at all. Are there any expert users who can advise how I could write the necessary expression to format and replace my dates? Thank you in advance.
mysql sql regex datetime notepad++
edited Nov 20 '18 at 10:10
Salman A
182k66339433
asked Nov 19 '18 at 20:00
asleniovasasleniovas
215
Good day everyone,
I have a data set in a csv file that has the following date formatting:
Tue Jun 16 18:01:56 BST 2009
Thu Aug 20 09:40:51 BST 2015
I am working on a MySQL database where the data needs to be uploaded into a DATETIME column thus the required format is presented below. The timezone abbreviations need to be removed completely, I am able to do that with find/replace as well as day abbreviations. So only the month abbreviations need to be converted to numbers and the year placed in the beginning:
YYYY-MM-DD HH:MM:SS
I know I can use CTRL+F to find and replace specific values in NP++. But as you might've guessed the date combinations in the file can be different. I have researched that it can be accomplished with a regular expression but I am not familiar with that at all. Are there any expert users who can advise how I could write the necessary expression to format and replace my dates? Thank you in advance.
mysql sql regex datetime notepad++
mysql sql regex datetime notepad++
edited Nov 20 '18 at 10:10
Salman A
182k66339433
asked Nov 19 '18 at 20:00
asleniovasasleniovas
215
edited Nov 20 '18 at 10:10
Salman A
182k66339433
asked Nov 19 '18 at 20:00
asleniovasasleniovas
215
edited Nov 20 '18 at 10:10
Salman A
182k66339433
edited Nov 20 '18 at 10:10
Salman A
182k66339433
edited Nov 20 '18 at 10:10
Salman A
182k66339433
182k66339433
asked Nov 19 '18 at 20:00
asleniovasasleniovas
215
asked Nov 19 '18 at 20:00
asleniovasasleniovas
215
asked Nov 19 '18 at 20:00
asleniovasasleniovas
215
215
MySQL's LOAD DATA INFILE. has ways to parse out CVS files and data within them using MySQL user variables.. Something like this should workLOAD DATA INFILE .... (<columns> @datetime) SET <columns>, @datetime = STR_TO_DATE(..., LEFT(...))
– Raymond Nijland
Nov 19 '18 at 20:06
add a comment |
MySQL's LOAD DATA INFILE. has ways to parse out CVS files and data within them using MySQL user variables.. Something like this should workLOAD DATA INFILE .... (<columns> @datetime) SET <columns>, @datetime = STR_TO_DATE(..., LEFT(...))
– Raymond Nijland
Nov 19 '18 at 20:06
MySQL's LOAD DATA INFILE. has ways to parse out CVS files and data within them using MySQL user variables.. Something like this should work
LOAD DATA INFILE .... (<columns> @datetime) SET <columns>, @datetime = STR_TO_DATE(..., LEFT(...))– Raymond Nijland
Nov 19 '18 at 20:06
MySQL's LOAD DATA INFILE. has ways to parse out CVS files and data within them using MySQL user variables.. Something like this should work
LOAD DATA INFILE .... (<columns> @datetime) SET <columns>, @datetime = STR_TO_DATE(..., LEFT(...))– Raymond Nijland
Nov 19 '18 at 20:06
add a comment |
1 Answer
1
active
oldest
votes
You could import the data in a temporary, varchar column as-is, then use STR_TO_DATE to convert it to date time:
UPDATE yourdata
SET actual_datetime = STR_TO_DATE(
CONCAT(SUBSTRING(temp_datetime, 5, 16), SUBSTRING(temp_datetime, 25, 4)),
'%b %d %H:%i:%s %Y'
)
Tests for STR_TO_DATE function
answered Nov 19 '18 at 20:14
Salman ASalman A
182k66339433
Thank you sir, works like a charm!
– asleniovas
Nov 19 '18 at 20:21
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
});
}
});
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%2f53381798%2fformatting-specific-date-to-required-format-in-notepad%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
You could import the data in a temporary, varchar column as-is, then use STR_TO_DATE to convert it to date time:
UPDATE yourdata
SET actual_datetime = STR_TO_DATE(
CONCAT(SUBSTRING(temp_datetime, 5, 16), SUBSTRING(temp_datetime, 25, 4)),
'%b %d %H:%i:%s %Y'
)
Tests for STR_TO_DATE function
answered Nov 19 '18 at 20:14
Salman ASalman A
182k66339433
Thank you sir, works like a charm!
– asleniovas
Nov 19 '18 at 20:21
add a comment |
You could import the data in a temporary, varchar column as-is, then use STR_TO_DATE to convert it to date time:
UPDATE yourdata
SET actual_datetime = STR_TO_DATE(
CONCAT(SUBSTRING(temp_datetime, 5, 16), SUBSTRING(temp_datetime, 25, 4)),
'%b %d %H:%i:%s %Y'
)
Tests for STR_TO_DATE function
answered Nov 19 '18 at 20:14
Salman ASalman A
182k66339433
Thank you sir, works like a charm!
– asleniovas
Nov 19 '18 at 20:21
add a comment |
You could import the data in a temporary, varchar column as-is, then use STR_TO_DATE to convert it to date time:
UPDATE yourdata
SET actual_datetime = STR_TO_DATE(
CONCAT(SUBSTRING(temp_datetime, 5, 16), SUBSTRING(temp_datetime, 25, 4)),
'%b %d %H:%i:%s %Y'
)
Tests for STR_TO_DATE function
answered Nov 19 '18 at 20:14
Salman ASalman A
182k66339433
You could import the data in a temporary, varchar column as-is, then use STR_TO_DATE to convert it to date time:
UPDATE yourdata
SET actual_datetime = STR_TO_DATE(
CONCAT(SUBSTRING(temp_datetime, 5, 16), SUBSTRING(temp_datetime, 25, 4)),
'%b %d %H:%i:%s %Y'
)
Tests for STR_TO_DATE function
answered Nov 19 '18 at 20:14
Salman ASalman A
182k66339433
answered Nov 19 '18 at 20:14
Salman ASalman A
182k66339433
answered Nov 19 '18 at 20:14
Salman ASalman A
182k66339433
answered Nov 19 '18 at 20:14
Salman ASalman A
182k66339433
182k66339433
Thank you sir, works like a charm!
– asleniovas
Nov 19 '18 at 20:21
add a comment |
Thank you sir, works like a charm!
– asleniovas
Nov 19 '18 at 20:21
Thank you sir, works like a charm!
– asleniovas
Nov 19 '18 at 20:21
Thank you sir, works like a charm!
– asleniovas
Nov 19 '18 at 20:21
add a comment |
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%2f53381798%2fformatting-specific-date-to-required-format-in-notepad%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
MySQL's LOAD DATA INFILE. has ways to parse out CVS files and data within them using MySQL user variables.. Something like this should work
LOAD DATA INFILE .... (<columns> @datetime) SET <columns>, @datetime = STR_TO_DATE(..., LEFT(...))– Raymond Nijland
Nov 19 '18 at 20:06