CODEWITHSUNDEEP
sql-servert-sql
unknown
unknown

Reputation: 461

SQL Server: return last day of the last year

Take a look at this code portion.

DECLARE @SQL VARCHAR(MAX), @DateWithDotsVARCHAR(10)

SELECT @DateWithDots = REPLACE(@Date, '-', '.')

IF OBJECT_ID('tempdb..##WL_Klijenti') IS NOT NULL 
    DROP TABLE ##WL_Klijenti

SELECT @SQL = '
    SELECT * 
     INTO ##WL_Klijenti
      FROM  OPENROWSET (''SQLOLEDB'',''Server=
     (local);TRUSTED_CONNECTION=YES;'',''SET FMTONLY OFF; SET NOCOUNT ON; 
    EXEC 
   '+DB_NAME()+'.dbo.sp_kbbl_WachLista_Priprema ''''' + @DateWithDots+ 
 ''''', ''''' + @DateWithDots + ''''', 0'')
AS tbl'
... the rest is less important

Second dateWithDots is not needed as user input but here I will have to pass the last day of the last year instead, in reference to the first @dateWithDots user will input. (This is due to some balance sheet calculations, everything works fine here I just have to set this adjustment.)

So somehow I will have to identify the current year beforehand, YEAR can be taken from the first @DateWithDots as this is requested user input parameter.

How can it be accomplished ?

SOLUTIONS:

@Cool_Br33ze's approach...

DECLARE @dateWithDots NVARCHAR(10)
SET @dateWithDots = '2018.01.18' --<< User Inputted date
SELECT LastDayLastYear = CAST(DATEADD(YEAR, 
            DATEDIFF(YEAR, -1, CAST(@dateWithDots AS DATE) )-1, -1) AS DATE)

My approach...

DECLARE @LastDay VARCHAR(MAX)
SET @LastDay = CONVERT(VARCHAR(4),SUBSTRING(@Datum,1,4)-1) + '.12' + '.31';

Upvotes: 1

Views: 8117

Answers (1)

Mazhar
Mazhar

Reputation: 3837

USE the correct the Datatypes for Dates

DECLARE @dateWithDots DATE
SET @dateWithDots = GETDATE()  --<< User Inputted date

SELECT LastDayLastYear = CAST(DATEADD(YEAR, 
            DATEDIFF(YEAR, -1, @dateWithDots )-1, -1) AS DATE)

Returns

2017-12-31

Using NVARCHAR for Dates

DECLARE @dateWithDots NVARCHAR(10)
SET @dateWithDots = '2018.01.18' --<< User Inputted date
SELECT LastDayLastYear = CAST(DATEADD(YEAR, 
                DATEDIFF(YEAR, -1, CAST(@dateWithDots AS DATE) )-1, -1) AS DATE)

Returns

2017-12-31

--Using Dynamic SQL - NVARCHAR is preferred over VARCHAR

DECLARE @SQL NVARCHAR(MAX) ;
SET @SQL = N'SELECT CAST(DATEADD(YEAR,DATEDIFF(YEAR, -1, CAST(@dateWithDots AS DATE) )-1, -1) AS DATE)'
EXEC sys.sp_executesql @SQL, N'@dateWithDots NVARCHAR(10)', @dateWithDots

Upvotes: 2

Related Questions