[sql] How to get the first and last date of the current year?

Using SQL Server 2000, how can I get the first and last date of the current year?

Expected Output:

01/01/2012 and 31/12/2012

This question is related to sql sql-server sql-server-2000

The answer is


---Lalmuni Demos---

create table Users
(
userid int,date_of_birth date
)

---insert values---

insert into Users values(4,'9/10/1991')

select DATEDIFF(year,date_of_birth, getdate()) - (CASE WHEN (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()),date_of_birth)) > getdate() THEN 1 ELSE 0 END) as Years, 
MONTH(getdate() - (DATEADD(year, DATEDIFF(year, date_of_birth, getdate()), date_of_birth))) - 1 as Months, 
DAY(getdate() - (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()), date_of_birth))) - 1 as Days,
from users

Every year has the 1 st as First date and 31 as the last date what you have to do is only attach the year to that day and month for example:-

 SELECT '01/01/'+cast(year(getdate()) as varchar(4)) as [First Day],
 '12/31/'+cast(year(getdate()) as varchar(4)) as [Last Day]

To get the first and the last day of the year, one can use the CONCAT function. The resulting value may be cast to any type.

CONCAT(YEAR(Getdate()),'-01-01') FirstOfYear,
CONCAT(YEAR(GETDATE()),'-12-31') LastOfYear

You can get the current year using DATEPART function, from the current date obtained using getUTCDate()

SELECT 
    '01/01/' + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate())), 
    '31/12/' + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))

select to_date(substr(sysdate,1, 4) || '01/01'), to_date(substr(sysdate,1, 4) || '12/31') 
from dual

For start date of current year:

SELECT DATEADD(DD,-DATEPART(DY,GETDATE())+1,GETDATE())

For end date of current year:

SELECT DATEADD(DD,-1,DATEADD(YY,DATEDIFF(YY,0,GETDATE())+1,0))

Check out this one:

select convert(varchar(12),(DateAdd(month,(Month(getdate())-1) * -1, DateAdd(Day,(Day(getdate())-1) * -1,getdate()))),103) as StartYear,
       convert(varchar(12),DateAdd(month,12 - Month(getdate()), DateAdd(Day,(31 - Day(getdate())),getdate())),103) as EndYear

It looks like you are interesting in performing an operation everything for a given year, if this is indeed the case, I would recommend to use the YEAR() function like this:

SELECT * FROM `table` WHERE YEAR(date_column) = '2012';

The same goes for DAY() and MONTH(). They are also available for MySQL/MariaDB variants and was introduced in SQL Server 2008 (so not for specific 2000).


In Microsoft SQL Server (T-SQL) this can be done as follows

--beginning of year
select '01/01/' + LTRIM(STR(YEAR(CURRENT_TIMESTAMP)))

--end of year
select '12/31/' + LTRIM(STR(YEAR(CURRENT_TIMESTAMP)))

CURRENT_TIMESTAMP - returns the sql server date at the time of execution of the query.

YEAR - gets the year part of the current time stamp.

STR , LTRIM - these two functions are applied so that we can convert this into a varchar that can be concatinated with our desired prefix (in this case it's either first date of the year or the last date of the year). For whatever reason the result generated by the YEAR function has prefixing spaces. To fix them we use the LTRIM function which is left trim.


Here's a fairly simple way;

SELECT DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AS 'First Day of Current Year';
SELECT DATEFROMPARTS(YEAR(GETDATE()), 12, 31) AS 'End of Current Year';

It's not sexy, but it works.


Another way: (Since SQL Server 2012)

SELECT
    DATEFROMPARTS(YEAR(GETDATE()), 1, 1) FirstDay,
    DATEFROMPARTS(YEAR(GETDATE()),12,31) LastDay

print Cast('1/1/' + cast(datepart(yyyy, getdate()) as nvarchar(4)) as date)

The best way to get First Date and Last Date of a year Is

SELECT CAST(CAST(YEAR(DATEADD(YEAR,-1,GETDATE())) AS VARCHAR) + '-' + '01' + '-' + '01' AS DATE) FIRST_DATE
SELECT CAST(CAST(YEAR(DATEADD(YEAR,-1,GETDATE())) AS VARCHAR) + '-' + '12' + '-' + '31' AS DATE) LAST_DATE

Try this:

DATE_FORMAT(NOW(),'01/01/%Y')
DATE_FORMAT(NOW(),'31/12/%Y')

The best way is to extract the current year then use concatenation like this :

SELECT CONCAT(year(now()), '-01-01') as start, -- fist day of current year
       CONCAT(year(now()), '-31-12') as end;   -- last day of current year

That gives you : start : 2020-01-01 and end : 2020-31-12 in date format.


SELECT DATEADD(DD,-DATEPART(DY,GETDATE())+1,GETDATE())

simply write:-

select convert (date,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0))

start date of the year.

select convert (date,DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()) + 1, -1))  

If it reaches the 1st of Jan you might it to be still last years date.

select
convert(date, DATEADD(yy, DATEDIFF(yy, 0,  DATEadd(day, -1,getdate())), 0), 103 ) AS StartOfYear,
convert(date, DATEADD(yy, DATEDIFF(yy, 0, DATEDIFF(day, -1,getdate()))+1, -1), 103 )AS EndOfYear

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2000

SQL get the last date time record INSERT INTO @TABLE EXEC @query with SQL Server 2000 How to get the first and last date of the current year? SQL select max(date) and corresponding value SQL where datetime column equals today's date? Is there a way to get a list of all current temporary tables in SQL Server? Grant execute permission for a user on all stored procedures in database? Is there a way to list open transactions on SQL Server 2000 database? Get the time of a datetime using T-SQL? select a value where it doesn't exist in another table