is there any unique SQL function to retrieve only year and month from the timestamp and based on tha-CodePudding

I TRIED THE BELOW MENTIONED CODE BUT IT GIVING DIFFERENT ANSWERS CODE USED

 $SELECT  MIN(DATENAME(MM,orders.occurred_at)) AS 'Month',
            MIN(DATENAME(YY,orders.occurred_at)) AS 'Year'
    FROM orders

TABLE

id  account_id  occurred_at
1   1001    2015-10-06 17:31:14.000
2   1001    2015-11-05 03:34:33.000
3   1001    2015-12-04 04:21:55.000
4   1001    2016-01-02 01:18:24.000
5   1001    2016-02-01 19:27:27.000
6   1001    2016-03-02 15:29:32.000
7   1001    2016-04-01 11:20:18.000
8   1001    2016-05-01 15:55:51.000
9   1001    2016-05-31 21:22:48.000
10  1001    2016-06-30 12:32:05.000
11  1001    2016-07-30 03:26:30.000
12  1001    2016-08-28 07:13:39.000
13  1001    2016-09-26 23:28:25.000
14  1001    2016-10-26 20:31:30.000
15  1001    2016-11-25 23:21:32.000
16  1001    2016-12-24 05:53:13.000
17  1011    2016-12-21 10:59:34.000
18  1021    2015-10-12 02:21:56.000
19  1021    2015-11-11 07:37:01.000
20  1021    2015-12-11 16:53:18.000

CodePudding user response：

I didn't understand if you want to see all of the results and just sort them so that the earliest instance would show up first in the list, or if you only want the earliest record by itself.

Sorting the list by the timestamp and displaying the Month() and Year() from the timestamp is easy enough:

SELECT
   ID
  ,Account_ID
  ,Occurred_At
  ,MONTH(Occurred_At) AS Occurred_Month
  ,YEAR(Occurred_At) AS Occurred_Year
FROM Orders
ORDER BY Occurred_At ASC -- This puts the oldest record at the top of the list
;

If you only want to retrieve only the oldest record, then you could do this:

SELECT TOP 1 -- This returns only the topmost record
   ID
  ,Account_ID
  ,Occurred_At
  ,MONTH(Occurred_At) AS Occurred_Month
  ,YEAR(Occurred_At) AS Occurred_Year
FROM Orders
ORDER BY Occurred_At ASC  -- This puts the oldest record at the top of the list
;

Presumably, you don't need to extract the month and year from Occurred_At in order to know which order was first... The timestamp gives you that information. But if, for some reason, you want to sort by the month and year instead of the full timestamp:

SELECT TOP 1
   ID
  ,Account_ID
--,Occurred_At -- If you don't need this line, you can remove it...
  ,MONTH(Occurred_At) AS Occurred_Month
  ,YEAR(Occurred_At) AS Occurred_Year
FROM Orders
ORDER BY Occurred_Year ASC, Occurred_Month ASC
;

CodePudding user response：

Try,

    SELECT  MONTH(occurred_at) AS Month,
            YEAR(occurred_at) AS Year
    FROM orders

   SELECT  FORMAT(occurred_at, 'MMMM') AS Month,
            FORMAT(occurred_at, 'yyyy') AS Year
    FROM orders

SELECT  
   FORMAT(occurred_at, 'y') AS 'Month and Year',
FROM orders