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!
declare @date date = GetDate()
select EOMONTH(DateAdd(month, -1, @Date))
DECLARE @Date DATE = EOMONTH(GETDATE(),-1);
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.
*/
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!
In that case it does not contain dates. It contains text that looks like dates.
Please provide sample source data and sample desired data
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:
What happens when you test the following by itself
Select '+YEAR(@Date)+/+MONTH(@Date)+'
Which is what you have as sample up top
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).
Thanks all but any idea why the below is not correct?
String = YEAR(@Date)+'/'+MONTH(@Date) -- e.g. '2020/02'
what do you get when you run
select YEAR(@Date)+'/'+MONTH(@Date)
in SSMS
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?
please try .. hope this helps ...
select
cast(YEAR(@Date) as varchar)
+
'/'
+
cast(MONTH(@Date) as varchar)
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)
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), '-', '/')
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.
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?
Did you try what @jeffw8713 posted?
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?
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) =