February 17, 2020 at 7:34 am
Hi,
I am having the following query to fetch day & year of a particular date.
Need to fetch Quarter & Year also. I tried in a similar fashion. But its not working. Please help on this.
DECLARE @minDate_Str NVARCHAR(30) = '01/01/2020' , @maxDate_Str NVARCHAR(30) = '31/12/2020'
DECLARE @minDate DATETIME, @maxDate DATETIME
SET @minDate = CONVERT(Datetime, @minDate_Str,103)
SET @maxDate = CONVERT(Datetime, @maxDate_Str,103)
SELECT CONVERT(NVARCHAR(10),@minDate,103) as Date, CONVERT(NVARCHAR(30), FORMAT(@minDate, 'ddd')) as DayofDate, CONVERT(NVARCHAR(30),FORMAT(@minDate, 'MMM')) as YearofDate;
February 17, 2020 at 7:37 am
Look up the date function date_part.
February 17, 2020 at 9:53 am
My requirement is as follows:
I am getting two input parameters in my stored procedure namely
Year & Quarter from the UI form.
@QuarterID bigint
@Year bigint
From the Year & Quarter, I need to find & assign Quarter start date & end date to two variables & do the calculations as below:
DECLARE @StartDate as nvarchar(20) = '2020-01-01', @Enddate as nvarchar(20) = '2020-03-31';
DECLARE @Result TABLE(DateVal NVARCHAR(30) NOT NULL, WeekdayName NVARCHAR(30) NOT NULL,MonthName NVARCHAR(30) NOT NULL, QuarterName NVARCHAR(30) NOT NULL, YearName NVARCHAR(30) NOT NULL )
INSERT INTO @Result(DateVal, WeekdayName,MonthName,QuarterName,YearName )
SELECT CONVERT(NVARCHAR(10),@StartDate,103), CONVERT(NVARCHAR(30), FORMAT(@StartDate, 'ddd')), CONVERT(NVARCHAR(30),FORMAT(@StartDate, 'MMM'));
WHILE @maxDate > @minDate
BEGIN
SET @minDate = (SELECT DATEADD(dd,1,@minDate))
INSERT INTO @Result(DateVal, WeekdayName,MonthName)
SELECT CONVERT(NVARCHAR(10),@minDate,103), CONVERT(NVARCHAR(30), FORMAT(@minDate, 'ddd')), CONVERT(NVARCHAR(30),FORMAT(@minDate, 'MMM')) ;
END
February 17, 2020 at 11:44 am
Best to test your code before you post it, just to check that it works! Did you try DATEPART as suggested?
John
February 17, 2020 at 2:59 pm
Year & Quarter from the UI form.
@QuarterID bigint
@Year bigint
Bigint really? It's like parking a VW in a blimp hanger, much too much. These could be smallint which would be valid for another 30,000+ years. Should be plenty! And is @QuarterID really an ID?
I think the others meant to point you to the DATEFROMPARTS(year, month, day) function.
To get the start of the quarter you already know the day (integer) is 1. The year is @year. The month could be calculated from the quarter by multiplying (quarter-1)*3 (there are 3 months in each calendar quarter). When the quarter=1 then the calculation returns 0 so I would make the month 'max((quarter-1)*3), 1)' Maybe there's a more elegant way to do this?
There are a few ways to get the end date. Here's one:
dateadd(qq, datediff(qq, 0, start_dt)+1, -1) qtr_end_dt
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 17, 2020 at 6:19 pm
There are proprietary temporal functions for formatting dates and working with them. Your code will never port to another SQL product. For example, the ANSI/ISO standard SQL uses the cast () function and not the old Sybase convert () proprietary function. Convert was put in the original Sybase product decades ago to keep COBOL programmers happy. The same is true for the Sybase MONEY data type.
.
My recommendation that first, you should read the SQL standards. This way you will find out that the only display format allowed in ANSI/ISO standard SQL is based on the ISO 8601 standard, and it looks like "yyyy-mm-dd", not the local dialect that you posted in your strings.
Then build a table to do data range lookups. We'll have more power and control than trying to do things as if you were still working with COBOL character strings.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 17, 2020 at 7:03 pm
You can calculate the start/end of quarter dates with the following:
Declare @quarter int = 3
, @year int = 2019;
Select FirstDayOfQuarter = dateadd(month, ((@quarter - 1) * 3), datetimefromparts(@year, 1, 1, 0, 0, 0, 0))
, LastDayOfQuarter = dateadd(day, -1, dateadd(month, ((@quarter - 1) * 3) + 3, datetimefromparts(@year, 1, 1, 0, 0, 0, 0)))
You can simplify this...
Declare @quarter int = 3
, @year int = 2019;
Select FirstDayOfQuarter = dateadd(month, ((@quarter - 1) * 3), y.FirstOfYear)
, LastDayOfQuarter = dateadd(day, -1, dateadd(month, ((@quarter - 1) * 3) + 3, y.FirstOfYear))
From (Values (datetimefromparts(@year, 1, 1, 0, 0, 0, 0))) As y(FirstOfYear)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 17, 2020 at 7:43 pm
I think you need a few functions for this solution. SCDecade almost had it right, to determine the @StartDate, try:
SET @StartDate = SELECT DATEFROMPARTS(@year, ((@QuarterID - 1) * 3) + 1, 1);
To determine the @EndDate try:
SET @EndDate = EOMONTH(@StartDate,2);
Then you can use DATENAME() function to get the name columns for your calendar table.
https://docs.microsoft.com/en-us/sql/t-sql/functions/datename-transact-sql?view=sql-server-ver15
There are other ways to build your calendar table besides a loop, are you familiar with what's called a Tally table?
February 17, 2020 at 7:51 pm
There are proprietary temporal functions for formatting dates and working with them. Your code will never port to another SQL product. For example, the ANSI/ISO standard SQL uses the cast () function and not the old Sybase convert () proprietary function. Convert was put in the original Sybase product decades ago to keep COBOL programmers happy. The same is true for the Sybase MONEY data type.
.
My recommendation that first, you should read the SQL standards. This way you will find out that the only display format allowed in ANSI/ISO standard SQL is based on the ISO 8601 standard, and it looks like "yyyy-mm-dd", not the local dialect that you posted in your strings.
Then build a table to do data range lookups. We'll have more power and control than trying to do things as if you were still working with COBOL character strings.
First, Mr. Celko, how many times have you gone into any one company multiple times in a short period of time, say 1 to 3 years, having to port their applications between various RDBM systems? I know of no company that changes database systems multiple times in a short period of time.
Second, refusing to make use of proprietary extensions of RDBM systems means you are leaving behind performance enhancing capabilities of those systems.
Third, if I had an application that needed to support MS SQL Server, Oracle, and PostgreSQL I would have 3 sperate code bases for my database objects so I could take advantage of the specific capabilities of each of those systems.
February 17, 2020 at 7:59 pm
February 17, 2020 at 9:29 pm
I think you need a few functions for this solution. SCDecade almost had it right, to determine the @StartDate, try:
SET @StartDate = SELECT DATEFROMPARTS(@year, ((@QuarterID - 1) * 3) + 1, 1);
Yes yes this returns the correct output. I was close but being close doesn't count for much in Sql.
To determine the @EndDate try:
SET @EndDate = EOMONTH(@StartDate,2);
Nice one. I hadn't noticed there was the second parameter for month offset.
There are other ways to build your calendar table besides a loop, are you familiar with what's called a Tally table?
Here's a link to the SSC script with Jonathan Roberts's insanely useful daterange function
https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply