Solved

Manipulate Date in SQL Statement

Posted on 2015-01-27
1
187 Views
Last Modified: 2015-01-27
Hello Experts,

I have a SQL table that keeps information about monthly Transactions by Day of Week as opposed to actual date. Below is an example:

Month              DOW
01-Jan-2014     Mon
01-Jan-2014     Tue
01-Jan-2014     ----
01-Jan-2014     Sun
----
01-Feb-2014     Mon
01-Feb-2014     Tue
---
01-Dec-2014     Mon
01-Dec-2014     Tue
---
01-Dec-2014     Sat
01-Dec-2014     Sun

As you can see, the day of month in the Month column is always 01.

I need to add a 3rd column for a new Date value that is dependent on the value of both Month and DOW columns. Basically, I need to convert day value from the generic 01 value to a value between 1-7:
01-MMM-YYY
02-MMM-YYY
03-MMM-YYY
04-MMM-YYY
05-MMM-YYY
06-MMM-YYY
07-MMM-YYY

The new date will be determined according to the Month and DOW values, so for example:


Month              DOW          New Date        
01-Jan-2014     Mon          06-Jan-2014
01-Jan-2014     Tue          07-Jan-2014
01-Jan-2014     Sun          05-Jan-2014
01-Feb-2014    Mon         03-Feb-2014
01-Feb-2014    Tue          04-Feb-2014
01-Dec-2014    Mon         01-Dec-2014
01-Dec-2014    Tue          02-Dec-2014

Ideally, the new column should be added to the Select statement as condition or CASE statement, based on the values in Month and DOW.

Many thanks in advance.
Hani
0
Comment
Question by:Mehawitchi
[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
1 Comment
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40572827
select [Month], DOW, datepart(dw,[Month]), dateadd(day, (7-datepart(dw,[Month])+
case [DOW] when 'Mon' then 2
when 'Tue' then 3
when 'Wed' then 4
when 'Thu' then 5
when 'Fri' then 6
when 'Sat' then 0
when 'Sun' then 1
end)%7, [Month]) as [New Date]
from myDates
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 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