Converting Char to Date Time

how to convert date and time from character to datetime type

As @Richard Scriven pointed out, you shouldn't be using as.Date because it's not a datetime class. Here are a few different ways:

DateTime <- "2007-02-01 00:00:00"
DateTime2 <- "02/01/2007 00:06:10"
## default format Y-m-d H:M:S
> as.POSIXct(DateTime,tz=Sys.timezone())
[1] "2007-02-01 EST"
> as.POSIXlt(DateTime,tz=Sys.timezone())
[1] "2007-02-01 EST"
##
## specify format m/d/Y H:M:S
> as.POSIXct(DateTime2,format="%m/%d/%Y %H:%M:%S",tz=Sys.timezone())
[1] "2007-02-01 00:06:10 EST"
> as.POSIXlt(DateTime2,format="%m/%d/%Y %H:%M:%S",tz=Sys.timezone())
[1] "2007-02-01 00:06:10 EST"
##
## using lubridate
library(lubridate)
> ymd_hms(DateTime,tz=Sys.timezone())
[1] "2007-02-01 EST"
> mdy_hms(DateTime2,tz=Sys.timezone())
[1] "2007-02-01 00:06:10 EST"

You don't have to specify format= for as.POSIXct and as.POSIXlt when you have the %Y-%m-%d %H:%M:%S format. In other cases, like %m/%d/%Y %H:%M:%S, you usually have to specify the format explicitly.

Trying to convert character to datetime using as_datetime() but getting a wrong format in R

You should change the format to match your data, your data is separated by dash (-), so the correct format would be %d-%m-%Y %R or %d-%m-%Y %H:%M. Notice that %R is equivalent to %H:%M.

library(lubridate)

as_datetime("01-07-2020 00:00", format = "%d-%m-%Y %R")
#> [1] "2020-07-01 UTC"
as_datetime("01-07-2020 17:59", format = "%d-%m-%Y %H:%M")
#> [1] "2020-07-01 17:59:00 UTC"

Learn more about date-time conversion format here

R: convert date from character to datetime

For me it works like this:

test <- "2016-04-10T12:21:25.4278624"
z <- as.POSIXct(test,format="%Y-%m-%dT%H:%M:%OS")

#output:
z
"2016-04-10 12:21:25 CEST"

The code is form here: converting datetime string to POSIXct date/time format in R

Convert Character date/time with am and pm to date/time format

Make the following changes:

• use %I for the hour
• use %p for the am/pm.
• ensure that the format pattern is in the precise pattern of the data -- it's not in the question
• you likely want POSIXct, not POSIXlt

thus we use this format

as.POSIXct("1/29/20 3:43pm", format = "%m/%d/%y %I:%M%p")
## [1] "2020-01-29 15:43:00 EST"

Converting char to date time

What you really want is strptime(). Try something like:

strptime(x$time, "%a %b %d %H:%M:%S %Y")

As an example of the interesting things you can do with strptime(), consider the following:

thedate <- "I came to your house at 11:45 on January 21, 2012."
strptime(thedate, "I came to your house at %H:%M on %B %d, %Y.")
# [1] "2012-01-21 11:45:00"

Trouble converting a char date time to MMDDYY10. using proc sql

Convert character datetime to numeric using the input function then use the datepart function to retrieve only the date part and apply proper formatting after.

proc sql;
create table want as
select datepart(input(date, anydtdtm.)) as date format=MMDDYY10.
from have;
quit;

---

proc sql; 
connect to hadoop(schema=pharm PROPERTIES='tez.queue.name=user_queue');
create table daily as 
select datepart(input(date, anydtdtm.)) as date format=MMDDYY10.
from connection to hadoop 
(select date from table limit 100)
; 
quit;

Converting Character to Date Class in R

You can use lubridate's ymd_hms.

date <- "2019-03-12T14:32:24.000-01:00"
date1 <- lubridate::ymd_hms(date)
date1
#[1] "2019-03-12 15:32:24 UTC"

Note that timezone has changed to UTC now and hence you see a different time.

If you only want the date you can use as.Date and extract the time part with format.

as.Date(date1)
#[1] "2019-03-12"

format(date1, '%T')
#[1] "15:32:24"

Convert character string into this specific date format?

so far your sample text is a valid datetime in mssql when I tried to cast. It seems there's some invalid data on your table. try using try_cast() to include those invalid data.

declare @ReviewDate varchar(max)='Mr John wrote a review in Oct 2017'

set  @ReviewDate = (RIGHT(@ReviewDate, 8))

select try_cast(@ReviewDate as datetime) as [ReviewDate2]

dbfiddle<>

---

Related Topics