Last date of the previous month in a variable

1

Hello!

Can you tell me please how to find the last date of the previous month, store it in a variable and use it in subsequent statements?

I found some SQL variable tutorials but they are extensive and do not explain some notiations. Can you provide a quick code?

Thanks!

2

declare @date date = GetDate()

select EOMONTH(DateAdd(month, -1, @Date))

1 Like
3

DECLARE @Date DATE = EOMONTH(GETDATE(),-1);

4

DECLARE @last_day_of_prev_month date
SET @last_day_of_prev_month = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
SELECT @last_day_of_prev_month

/* The standard/best practice pattern for getting a specific time interval in SQL Server is:
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
Then, to get the last day of the month, just subtract a day.
I say stick to the standard patterns as it will make the code much easier to follow and/or change later.

Of course the more general pattern is:
DATEADD(<time_period>, DATEDIFF(<time_period>, 0, GETDATE()), 0)

So, for example, if you want to get the first day of the current year:
DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
Or for the start of today:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
Or for the start of the current hour:
DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)

Notice how consistent that code is.
*/

1 Like
5

Thanks! I am facing an issue. I want to use the @Date variable as a match for a column that may contain text or dates.

For example, I have [Col1] that contains text in the format YYYY/MM. How can I use the @Date to match such column?

Is it [Col1] = YEAR(@Date)/MONTH(@Date) -- or this will actually divide the year by the month?
Is is [Col1] = '+YEAR(@Date)+/+MONTH(@Date)+' -- or anything else?

Also, how can I return two digit month and single digit month from a date? Eg. for February, 2 or 02?

Thanks!

6

In that case it does not contain dates. It contains text that looks like dates.

Please provide sample source data and sample desired data

7

The sample data is:
[Col1] = 2020/01

But my question is, how to get the month and year of the @Date variable and use it in two ways:

  1. for date fields
  2. for text fields looking like dates (e.g. 'YYYY/MM')
8

What happens when you test the following by itself

Select '+YEAR(@Date)+/+MONTH(@Date)+'

Which is what you have as sample up top

9

The easiest way to validate your text columns against a variable would be to attempt to convert the date string to a date.

Declare @stringDate varchar(20) = '2020/01';
Select try_cast(concat(replace(@stringDate, '/', ''), '01') As date)

The other option is to convert your @date to a string and compare:

Select replace(convert(char(7), @stringDate, 121), '-', '/')

As for getting the end of month - another option:

Select dateadd(month, datediff(month, -1, getdate()) - 1, -1)   -- end of previous month
     , dateadd(month, datediff(month, -1, getdate()) + 0, -1)   -- end of current month
     , dateadd(month, datediff(month, -1, getdate()) + 1, -1);  -- end of next month

For variable declaration - I prefer using EOMONTH as it is simpler code, and which one to use in a query depends on the expected output. If using to compare against a datetime column - use the calculation above and if used in a comparison with a date data type use EOMONTH.

If you are converting a column to the end of month then choose the version that returns the correct data type. For datetime2 or datetimeoffset - which require explicit or implicit cast/convert in either method - much easier to use EOMONTH with an explicit cast/convert. Ex: cast(eomonth(sysdatetime(), -1) as datetime2).

For the first of the month...the standard formula works for any variable assignment...but using EOMONTH is just as easy: SELECT dateadd(day, 1, eomonth(getdate(), -1). The same issues apply - if using in a query use the version that returns the appropriate data type or use an explicit cast/convert (which is also required for datetime2 or datetimeoffset).

10

Thanks all but any idea why the below is not correct?

String = YEAR(@Date)+'/'+MONTH(@Date) -- e.g. '2020/02'

11

what do you get when you run

select YEAR(@Date)+'/'+MONTH(@Date)

in SSMS

12

It seems it tries to add '/' as a number instead of concatenate it.

So how do I produce the 2020/01 format as a string?
I suppose I need to use CONVERT(YEAR(@Date),varchar) but not sure how to add the ' characters, any hint?

13

please try .. hope this helps ...

select
cast(YEAR(@Date) as varchar)
+
'/'
+
cast(MONTH(@Date) as varchar)

1 Like
14

Thanks but don't I need to put the string below between ' marks so that it is considered as 'string'?
cast(YEAR(@Date) as varchar)+'/'+cast(MONTH(@Date) as varchar)

15

No - you don't need to add the single-quotes unless you are building dynamic SQL. The return of the calculation is a string (varchar...) and SQL Server will interpret the values that way.

The problem with using YEAR and MONTH functions is that they return INT data type, not a string data type. This - YEAR(@date) / MONTH(@date) - is going to perform math on the year and month values because they are INT so you get 2020 / 02 = 1010 (2020 divided by 2). Attempting to build a string like this: YEAR(@date) + '/' + MONTH(@date) will fail because you cannot convert '/' to an integer.

To repeat myself - the best way is to do this is to convert your @date variable to a string (varchar) and then use that to compare to the values in the columns.

Declare @Date date = '2020-02-01';

 Select convert(char(7), @Date, 111)
      , replace(convert(char(7), @Date, 121), '-', '/')
3 Likes
16

I concur with Jeff that formatting using a style that meets your requirements is the best approach. This page, where the various styles are listed is one of the pages I refer to very often.

17

Thanks for this but I would like to follow a bit different strategy.
I would like to get the month and year from @date and use these values to construct the 2020/01 string.
Isn't that possible?

I appreciate the strategy to convert the data into a string and replace the hyphens with dashes but I wonder if the above is possible. Any idea?

18

Did you try what @jeffw8713 posted?

1 Like
19

I am actually facing a very strange issue. The below:

DECLARE @LDOfLM date = '2020-11-30' -- SPECIFY THE LAST DATE OF THE LAST MONTH IN YYYY-MM-DD FORMAT
DECLARE @LMAndYear varchar =
CONCAT(CONVERT(varchar,YEAR(@LDOfLM)),'/',IIF(MONTH(@LDOfLM)<10,CONCAT('0',CONVERT(varchar,MONTH(@LDOfLM))),CONVERT(varchar,MONTH(@LDOfLM))))
PRINT @LMAndYear
PRINT CONCAT(CONVERT(varchar,YEAR(@LDOfLM)),'/',IIF(MONTH(@LDOfLM)<10,CONCAT('0',CONVERT(varchar,MONTH(@LDOfLM))),CONVERT(varchar,MONTH(@LDOfLM))))

Returns:
2
2020/11

How can the printing of the same thing returns different things?

20

because

DECLARE @LastMonthAndYear varchar =

no length specified for varchar so it defaults to length 1 varchar(1)

select len(@LastMonthAndYear)

should be

DECLARE @LastMonthAndYear varchar(7) =