HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

What is the best way to rebuild a date from integer inputs?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
inputsthewhatwaydaterebuildfromintegerbest

Problem

I've got a bunch of financial reports, and we want to be able to pass them two inputs (year and quarter) as variables.

I'm doing it this way, but I really don't like it:

declare @quarter int,
    @year int,
    @date date

    set @quarter = 4
    set @year = 2018

    set @date = cast(@year as varchar(4)) + '-01-01'
    set @date = dateadd(quarter, @quarter - 1, @date)

    print @date


Question What is the best way to reconstruct a date from integer inputs?

desired result:

2018-10-01

Solution

How about

declare @quarter int = 4
declare @year int = 2018

select datefromparts(@year,(@quarter-1)*3+1,1)


or if you're still using SQL 2008:

select dateadd(month,(@quarter-1)*3,dateadd(year, @year-2018,'20180101'))

Code Snippets

declare @quarter int = 4
declare @year int = 2018

select datefromparts(@year,(@quarter-1)*3+1,1)
select dateadd(month,(@quarter-1)*3,dateadd(year, @year-2018,'20180101'))

Context

StackExchange Database Administrators Q#226838, answer score: 12

Revisions (0)

No revisions yet.