?
Solved

Need help with Date query -- T-SQL

Posted on 2013-06-03
12
Medium Priority
?
444 Views
Last Modified: 2013-06-09
Hello Experts,

I need your help in writing a query  to populate "On a given date I want the last month of the previous quarter from the column "CalendarDate".

See below:

CalendarDate
 For Example:  If I run the query today. i.e., 2013-06-03, I should get the date "2013-03-01"

If I run the query for a specific date instead of today, for example: "2013-07-10" , I should get "2013-06-01"

Please let me know if my question is not clear enough!

Thanks in advance
0
Comment
Question by:ravichand-sql
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 750 total points
ID: 39217847
This will do:

declare @dt date
set @dt = '20130701'
select case when datepart(q, @dt) = 1 then convert(date, convert(varchar, datepart(yy, @dt) - 1) + '1201') 
       else convert(date, convert(varchar, datepart(yy, @dt)) + replace(str((datepart(q, @dt)-1) * 3, 2), ' ', '0') + '01') end

Open in new window


SQL Fiddle
0
 
LVL 1

Expert Comment

by:Ray Phan
ID: 39217882
Hi !
Please try this

 
select CAST(CAST(dPart.yyyy AS varchar)+ '-' + CAST(dPart.mm AS varchar)+ '-' + CAST(dPart.dd AS varchar) AS DATE) from (select datepart(yy, CalendarDate) as yyyy, (datepart(qq, CalendarDate)-1)*3 as mm, '01' as dd from YourTableName) as dPart

Hope this help.
0
 
LVL 25

Expert Comment

by:chaau
ID: 39217947
@khiemp: Have you tested it for the CalendarDate in the first quarter?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 9

Expert Comment

by:sarabhai
ID: 39217975
try this

SELECT FirstDayOfQuarter = DATEADD(quarter,DATEDIFF(quarter,1,GETDATE()), -DAY(DATEDIFF(quarter,1,GETDATE()))-1 )
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 750 total points
ID: 39217978
deduct modulus of month % 3 , and deduct (one less than) the day , of a date
declare @dt date
set @dt = '20130721'
select dateadd(day,-datepart(day,@dt) + 1,dateadd(month,- datepart(month,@dt) % 3,@dt))
;

select
  adatefield
, dateadd(day,-datepart(day,adatefield) + 1,dateadd(month,- datepart(month,adatefield) % 3,adatefield))
from yourtable
;

Open in new window

e.g.
2013-01-21
month = 7
modulus 7 % 3  = 1, deduct 1 month = June, ( nb & this also sorts out the year)

day = 21
deduct (one less than) 21 = (21-20) = 1

see: http://sqlfiddle.com/#!3/65395/2
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 39217980
for custom date

DECLARE @dt DATETIME
SET @dt=GETDATE()
SELECT FirstDayOfQuarter = DATEADD(quarter,DATEDIFF(quarter,1,@dt), -DAY(DATEDIFF(quarter,1,@dt))-1 )
0
 
LVL 1

Expert Comment

by:Ray Phan
ID: 39218016
@chaau:Thanks for you notice, so I have modified my query string :


select CAST((CASE WHEN dPart.mm < 1 THEN CAST(dPart.yyyy-1 AS varchar ) ELSE CAST(dPart.yyyy AS varchar) END)+ '-' +(CASE WHEN dPart.mm < 1 THEN '12' ELSE CAST(dPart.mm AS varchar) END  )+ '-' + CAST(dPart.dd AS varchar) AS DATE)
from (select datepart(yy,CalendarDate) as yyyy, (datepart(qq,CalendarDate)-1)*3 as mm, '01' as dd from YourTableName) as dPart
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39218021
http://sqlfiddle.com/#!3/caa6e/1

3 of the 4 compared (ID: 39217882 not included as it needed more effort to include, sorry)
0
 
LVL 25

Expert Comment

by:chaau
ID: 39218026
@PortletPaul: cool, thanks for the effort. Looks like I am winning so far. However, I like your approach more, as I think it will be better performance wise. I think, it is very easy to fix.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39218031
I noticed the difference too :)
I was quite happy with mine, but it may need tweaking... feel free to play with it

anyone can join in :)
0
 
LVL 25

Expert Comment

by:chaau
ID: 39218045
@PortletPaul: your will work if you do this:

dateadd(day, - datepart(day, adatefield) + 1, dateadd(month, - (datepart(month, adatefield) + 2) % 3 - 1, adatefield))

Open in new window


See last column: http://sqlfiddle.com/#!3/caa6e/6
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39218109
@chaau
( ... + 2) ... - 1
amazing what a two integers will do :)
nicely done
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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