Converting Year and Month ("Yyyy-Mm" Format) to a Date

Converting year and month (yyyy-mm format) to a date?

Try this. (Here we use text=Lines to keep the example self contained but in reality we would replace it with the file name.)

Lines <- "2009-01  12
2009-02 310
2009-03 2379
2009-04 234
2009-05 14
2009-08 1
2009-09 34
2009-10 2386"

library(zoo)
z <- read.zoo(text = Lines, FUN = as.yearmon)
plot(z)

The X axis is not so pretty with this data but if you have more data in reality it might be ok or you can use the code for a fancy X axis shown in the examples section of ?plot.zoo .

The zoo series, z, that is created above has a "yearmon" time index and looks like this:

> z
Jan 2009 Feb 2009 Mar 2009 Apr 2009 May 2009 Aug 2009 Sep 2009 Oct 2009
12 310 2379 234 14 1 34 2386

"yearmon" can be used alone as well:

> as.yearmon("2000-03")
[1] "Mar 2000"

Note:

  1. "yearmon" class objects sort in calendar order.

  2. This will plot the monthly points at equally spaced intervals which is likely what is wanted; however, if it were desired to plot the points at unequally spaced intervals spaced in proportion to the number of days in each month then convert the index of z to "Date" class: time(z) <- as.Date(time(z)) .

how to get a date in YYYY-MM format?

Assuming that column is defined as timestamp or timestamptz (which it really should be) then you can use to_char() to format the output:

select to_char(the_timestamp_column, 'yyyy-mm')
from the_table;

Convert date to YYYYMM format

SELECT LEFT(CONVERT(varchar, GetDate(),112),6)

Format JavaScript date as yyyy-mm-dd

You can do:

function formatDate(date) {
var d = new Date(date),
month = '' + (d.getMonth() + 1),
day = '' + d.getDate(),
year = d.getFullYear();

if (month.length < 2)
month = '0' + month;
if (day.length < 2)
day = '0' + day;

return [year, month, day].join('-');
}

console.log(formatDate('Sun May 11,2014'));

Convert a yyyymm format to date in R?

A date should always have a given day, therefore consider to add the day at the end of YYYYMM (in the example below 01 the first day of the month). Convert the numeric value to character and the character to a Date:

as.Date(paste0(as.character(201603), '01'), format='%Y%m%d')

convert numeric week of year to a date (yyyy-mm-dd) in hiveql

To get accurate date you need to provide also week day along with year and week number in a year.

select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43, 7', 'yyyy, w, u'), 'yyyy-MM-dd');

Returns:

43   2020-10-18

It looks like week number in a year counted from Sundays and day number in a week is counted from Mondays because Monday is 19th:

select date_format('2020-10-18','w u'), from_unixtime(unix_timestamp('2020, 43, 1', 'yyyy, w, u'), 'yyyy-MM-dd');

returns

43  2020-10-19

If that is true, you can fix it by subtracting 60*60*24 from unix_timestamp:

select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43, 1', 'yyyy, w, u')-60*60*24, 'yyyy-MM-dd');

Returns:

43  2020-10-18

UPDATE: Surprisingly, if not providing day in a week, only year and week number, it works also counting Sunday as a week day by default but it will be not correct for other dates for example 2020-01-20, it will return the same Sunday 2020-01-18, check it yourself:

select date_format('2020-10-18','w'), from_unixtime(unix_timestamp('2020, 43', 'yyyy, w'), 'yyyy-MM-dd');

returns:

43  2020-10-18

So, if you do not have day in a week and do not need absolutely accurate date, then use

from_unixtime(unix_timestamp('2020, 43', 'yyyy, w'), 'yyyy-MM-dd');

Or like this (year and week number are selected from the table):

  select from_unixtime(unix_timestamp(concat(col_year, ', ', col_week), 'yyyy, w'), 'yyyy-MM-dd') from your_table;


Related Topics



Leave a reply



Submit