sath350163
asked on
First day of the give Month and Year
Hello,
I have a this stored procedure to which the year and month Number is pass as integer
input.
For the SQL within that stored procedure, using the input month and Year (which is in integer data type), I need to derive a DATE as first day of that input month and year.
But is there a better way to achieve this?
Is there a date function which would take month and year, and return me the first day in that month/year, and also the value should be of type DATE?
Thanks!
I have a this stored procedure to which the year and month Number is pass as integer
input.
For the SQL within that stored procedure, using the input month and Year (which is in integer data type), I need to derive a DATE as first day of that input month and year.
Eg:
CREATE PROCEDURE test (@i_Year INT, @i_Month INT)
BEGIN
DECLARE @v_date DATE;
SET @v_date = <need to assign 1st day of the input month/year i.e, '20140101'>
END
Input values passed to the stored procedure:
@i_Year = 2014
@i_Month = 1
Below is what I figured out:
SELECT
CONVERT(DATE, (CONVERT(VARCHAR,@v_Year)+CONVERT(VARCHAR,@v_Month)+'01')
)
But is there a better way to achieve this?
Is there a date function which would take month and year, and return me the first day in that month/year, and also the value should be of type DATE?
Thanks!
Hi,
If you have a datetime, the easiest, fastest way is to keep it as a date time like this:
select dateadd( year, datediff( year, 0, getdate()), 0 )
Works for years, quarters, months, days, hours, minutes.
HTH
David
PS converting to/from chars is slow - okay for a one-off, but not so much when grouping a bunch of transactions.
If you have a datetime, the easiest, fastest way is to keep it as a date time like this:
select dateadd( year, datediff( year, 0, getdate()), 0 )
Works for years, quarters, months, days, hours, minutes.
HTH
David
PS converting to/from chars is slow - okay for a one-off, but not so much when grouping a bunch of transactions.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi David,
of course that works, too. My benchmark says it's taking about two times as long, though. two vs one dateadd, quite logical.
So what's wrong with dateadd( month, (@i_Year-1900)*12+@i_Month -1, 0 )?
Too complicated?
Bye, Olaf.
of course that works, too. My benchmark says it's taking about two times as long, though. two vs one dateadd, quite logical.
So what's wrong with dateadd( month, (@i_Year-1900)*12+@i_Month
Too complicated?
Bye, Olaf.
ASKER
Appreciate the response from both of you.
Both the below approaches works well for my use case.
Select dateadd( month, (@i_Year-1900)*12+@i_Month -1, 0 )
select dateadd( month, @month - 1, dateadd( year, @year - 1900, 0 ))
Thanks a lot!
Both the below approaches works well for my use case.
Select dateadd( month, (@i_Year-1900)*12+@i_Month
select dateadd( month, @month - 1, dateadd( year, @year - 1900, 0 ))
Thanks a lot!
Thanks a bunch,
you could take it one step further and have a date generation function with year,month,day by using
dateadd(day, @day-1, dateadd( month, @month - 1, dateadd( year, @year - 1900, 0 )))
In your sepcific case the @day always is 1 and so the outer dateadd isn't needed. It's much harder to replace that with a single dateadd on day basis, as you'd need to compute days per year and month, with leap years and the differing month lengths etc.
It's easy enough to do with months only, though, and instead of starting from 0 start from day-1. So a generic solution with @year, @month and @day parameters could also be
dateadd(month, (@year - 1900) * 12 + @month - 1 , @day - 1)
Which would compare to C# DateTime(Int32, Int32, Int32) for year,month, day ints.
It's a pity T-SQL doesn't offer that simple date function in the first place, which was your original good question.
Bye, Olaf.
you could take it one step further and have a date generation function with year,month,day by using
dateadd(day, @day-1, dateadd( month, @month - 1, dateadd( year, @year - 1900, 0 )))
In your sepcific case the @day always is 1 and so the outer dateadd isn't needed. It's much harder to replace that with a single dateadd on day basis, as you'd need to compute days per year and month, with leap years and the differing month lengths etc.
It's easy enough to do with months only, though, and instead of starting from 0 start from day-1. So a generic solution with @year, @month and @day parameters could also be
dateadd(month, (@year - 1900) * 12 + @month - 1 , @day - 1)
Which would compare to C# DateTime(Int32, Int32, Int32) for year,month, day ints.
It's a pity T-SQL doesn't offer that simple date function in the first place, which was your original good question.
Bye, Olaf.
Open in new window
Change parameters to char(4) and char(2) or convert @iYear*100+@i_Month to a char(6) to concatenate '01'.
Bye, Olaf.
edit: ... or create a char(8) for implicit conversion:
Open in new window
This just won't work with years before 1000, you'd have to add leading zeros, then.
Bye, Olaf.