Solved

Need help with Date query -- T-SQL

Posted on 2013-06-03
12
443 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 250 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 250 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

696 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