Solved

First day of the give Month and Year

Posted on 2014-02-22
7
603 Views
Last Modified: 2014-02-24
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.

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')
               )

Open in new window


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!
0
Comment
Question by:sath350163
  • 4
  • 2
7 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39880237
ISO date literals, eg '20140101' can be used - without CONVERT. There's implicit conversion done, so this works:

declare @v_date as date;

set @v_date ='20140301'

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:
set @v_date = convert(char(8),@i_Year*10000+@i_Month*100+1)

Open in new window


This just won't work with years before 1000, you'd have to add leading zeros, then.

Bye, Olaf.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39880306
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.
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 250 total points
ID: 39880319
> dateadd( year, datediff( year, 0, getdate()), 0 )
That gives the first day of the current year.

dateadd( month, datediff( month, 0, getdate()), 0 )
gives the first day of the current month.

That's fine for these special cases, but no answer for the parameterisation of @i_Year, @i_Month.

So, Dave, you should explain 0 is taken as SQL Server default date 1900-01-01, so you could use the number of months to add to january, 1st 1900:

Select dateadd( month, (@i_Year-1900)*12+@i_Month-1, 0 )

eg:

declare @i_Year int, @i_Month int

set @i_Year = 2011
set @i_Month = 6

Select dateadd( month, (@i_Year-1900)*12+@i_Month-1, 0 )

Open in new window


Bye, Olaf.

Edit: I just did a little benchmark. Turns out dateadd is faster, that's no surprise. But it's just taking 40% of the char conversion.

I'm a bit surprised how much faster that is, actually. I wouldn't have thought a dateadd solution is more than twice as fast, even though it's two conversions (int->string->date) vs one date computation (dateadd) additional to a simple numeric computation, which would be neglectable in both cases.

declare @v_date date;
declare @t0 datetime;
declare @iJunk int;
declare @offset int;

use tempdb;
DECLARE @Dates table (iYear int, iMonth int);

Insert Into  @Dates
SELECT TOP 10000000 
RAND(checksum(newid()))*100+1950 as iYear, 
RAND(checksum(newid()))*12+1 as iMonth
FROM MASTER..spt_values a, MASTER..spt_values b;

--time to query all rows 
Select @t0=getdate();
Select @iJunk= iYear, @iJunk=iMonth from  @Dates;
--this is an offset time both of the following queries take just to query the data:
Select @offset = datediff(ms,@t0,getdate()); 
--subtract this time from both of the following results to get the net time for conversions.

--time to create a date with dateadd from 1900-01-01
Select @t0=getdate();
Select @v_date = dateadd( month, (iYear-1900)*12+iMonth-1, 0 ) from  @Dates;
Select datediff(ms,@t0,getdate())-@offset;

--time to create a date with conversion of int to char and implicit conversion of char to date
Select @t0=getdate();
Select @v_date = convert(char(8),iYear*10000+iMonth*100+1) from @Dates;
Select datediff(ms,@t0,getdate())-@offset;

Open in new window

0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
ID: 39880896
Hi,

Howabout

declare @year int
declare @month int

set @year = 2011
set @month = 7

select dateadd( month, @month - 1, dateadd( year, @year - 1900, 0 ))

Open in new window

HTH
  David
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39880982
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.
0
 

Author Comment

by:sath350163
ID: 39881543
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!
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39882220
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to SQL Trace a SPECIFIC query 24 57
sql query help 4 44
email the result out from a T-SQL queries 29 62
testing sql16 on win10 vs OS16 2 34
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now