Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

splitting a datetime column into year, month and week

I want to split a datetime column so that the year and the month both have their own column in a select statement output. I also want to have a column by week of the year, as opposed to specific date.

Basically, I want separate year, month, and week columns to show up in my select statement output.

like image 354
Laura Harmon Avatar asked Oct 02 '13 14:10

Laura Harmon


People also ask

How do you split a date column to month and year in Python?

Create a list of dates and assign into dataframe. Apply str. split function inside '/' delimiter to df['date'] column. Assign the result to df[[“day”, “month”, “year”]].

How do I split a date into a month and year in SQL?

To get the year and the month columns, use the EXTRACT(part FROM date) function. In this solution, the part argument is replaced by YEAR and MONTH to get the year and the month separately, each in its own column.

How do I split a date into a month and year in Excel?

1. Select the dates you want to split, click Kutools > Text > Split Cells. 2. In the Split Cells dialog, check Split to Columns checkbox, then check Other in Split by section, type / into the next textbox.


1 Answers

Try using the DatePart function as shown in the following:

select
  datepart(year,Mydate), 
  datepart(month,Mydate),
  datepart(week,Mydate)
From
  MyTable

Note: If you need to calculate the week number by ISO 8601 standards then you'll need to use datepart(iso_week,Mydate)

You could also look at the DateName function

select
  datename(month,Mydate)
From
  MyTable
like image 65
DMK Avatar answered Oct 25 '22 10:10

DMK