SQL Server Extracting DAY, WEEK, MONTH, YEAR from a date column

8ehkhllq 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(165)

I have a table like this, named Tour Details .
| dealdate | Tours | Weeknumber | Country | SubVenue |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 2022-01-12 | 3 | 49 | India | SP1 |
| 2022-11-25 | 4 | 48 | India | SP2 |
| 2022-10-14 | 5 | 42 | India | SP3 |
| 2023-01-05 | 3 | 2 | India | SP1 |
| 2023-01-05 | 4 | 2 | India | SP2 |
| 2023-01-06 | 5 | 2 | US | SP1 |
| 2023-02-24 | 3 | 9 | US | SP2 |
| 2022-02-12 | 4 | 7 | US | SP3 |
| 2023-02-19 | 5 | 8 | US | SP1 |
| 2023-02-27 | 6 | 10 | US | SP2 |

I need to create a table summary tour details . This table should have information like the DAY , WEEK , MONTH , YEAR derived from the dealdate from Tour Details for every record in the column SubVenue .

Grp	Day	Week	Month	Year
Tours SP1	1	49	12	2022
Tours SP2	3	50	11	2023
Tours SP1	2	3	1	2022
Tours SP3	12	4	2	2023

Any help would be greatly appreciated

sql-server

来源：https://stackoverflow.com/questions/76483090/extracting-day-week-month-year-from-a-date-column

1条答案

按热度按时间

ghhaqwfi1#

This is an example for datatype date . But it will also work for varchar .

create table [Tour Details](
  dealdate date,
  Tours int,
  Weeknumber int,
  Country varchar(100),
  SubVenue varchar (5)
)

insert into [Tour Details]
values
('2022-12-01',  3,  49, 'India',    'SP1'),
('2022-11-25',  4,  48, 'India',    'SP2'),
('2022-10-14',  5,  42, 'India',    'SP3'),
('2023-01-05',  3,  2,  'India',    'SP1'),
('2023-01-05',  4,  2,  'India',    'SP2'),
('2023-01-06',  5,  2,  'US',   'SP1'),
('2023-02-24',  3,  9,  'US',   'SP2'),
('2022-02-12',  4,  7,  'US',   'SP3'),
('2023-02-19',  5,  8,  'US',   'SP1'),
('2023-02-27',  6,  10, 'US',   'SP2')

select 'Tours '+SubVenue as Grp, 
       datepart(day,dealdate) as [Day],
       datepart(week,dealdate) as [Week],
       datepart(month,dealdate) as [Month],
       datepart(year,dealdate) as [Year]
into [summary tour details]
from [Tour Details] 

select * from [summary tour details]

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Extracting DAY, WEEK, MONTH, YEAR from a date column

1条答案

相关问题

热门标签

最新问答