How to get the first and last date of the current year

Question

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

User · Answer

---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    gt  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

User · Answer

Another way   Since SQL Server 2012   SELECT     DATEFROMPARTS YEAR GETDATE     1  1  FirstDay      DATEFROMPARTS YEAR GETDATE    12 31  LastDay

User · Answer

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

User · Answer

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

User · Answer

select to date substr sysdate 1  4      01 01    to date substr sysdate 1  4      12 31    from dual

User · Answer

SELECT    DATEADD yy  DATEDIFF yy  0  GETDATE     0  AS StartOfYear     DATEADD yy  DATEDIFF yy  0  GETDATE      1  -1  AS EndOfYear   The above query gives a datetime value for midnight at the beginning of December 31  This is about 24 hours short of the last moment of the year  If you want to include time that might occur on December 31  then you should compare to the first of the next year  with a  lt  comparison  Or you can compare to the last few milliseconds of the current year  but this still leaves a gap if you are using something other than DATETIME  such as DATETIME2    SELECT    DATEADD yy  DATEDIFF yy  0  GETDATE     0  AS StartOfYear     DATEADD yy  DATEDIFF yy  0  GETDATE      1  -1  AS LastDayOfYear     DATEADD yy  DATEDIFF yy  0  GETDATE      1  0  AS FirstOfNextYear     DATEADD ms  -3  DATEADD yy  DATEDIFF yy  0  GETDATE      1  0   AS LastTimeOfYear   Tech Details  This works by figuring out the number of years since 1900 with DATEDIFF yy  0  GETDATE    and then adding that to a date of zero   Jan 1  1900  This can be changed to work for an arbitrary date by replacing the GETDATE   portion or an arbitrary year by replacing the DATEDIFF      function with  Year - 1900     SELECT    DATEADD yy  DATEDIFF yy  0   20150301    0  AS StartOfYearForMarch2015     DATEADD yy  2015 - 1900  0  AS StartOfYearFor2015

User · Answer

print Cast  1 1     cast datepart yyyy  getdate    as nvarchar 4   as date

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT DATEADD DD -DATEPART DY GETDATE    1 GETDATE

User · Answer

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

User · Answer

Try this   DATE FORMAT NOW    01 01  Y   DATE FORMAT NOW    31 12  Y

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2000