Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dotsofcolor
Starting Member
12 Posts |
 Posted - 2013-05-22 : 15:13:11
|
| I have a field that starts out to be a date showing as 113138 which is actually 5/18/2013. I use the following to convert it to an actual date value. DATE(CHAR(CANCEL-DTE+1900000)) AS Cancel-DateIt converts 113138 to 5/18/2013 for me. However, I need to know how to get the Monday's date from the beginning of the week which should be 5/13/2013 in a separate column using the above line. I have tried several things but not getting anywhere.The original date 113138 is coming from a DB2 source. Currently I'm writing the query in TOAD.I'm pretty new at this so I appreciate any help, thanks. |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-05-22 : 16:44:26
|
As you're using db2 engine, you're better of asking a db2 forum. This forum is for mssql engine.That said, the solution you're seeking, might be solved with this:date(char(cancel_dte+1900000))-(dayofweek_iso(date(char(cancel_dte+1900000)))-1) days as cancel_date |
 |
|
|
dotsofcolor
Starting Member
12 Posts |
Posted - 2013-05-22 : 17:16:34
|
That worked perfect! I was missing out on at least half of what you provided. I really appreciate the help.  I thought about posting this in the DB2 forum but since I'm using it in BIDS then SSRS in the end, wasn't sure if I should start here. I will start there first next time.Thanks again bitsmed. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-23 : 00:35:19
|
quote:
Originally posted by dotsofcolor
That worked perfect! I was missing out on at least half of what you provided. I really appreciate the help. I thought about posting this in the DB2 forum but since I'm using it in BIDS then SSRS in the end, wasn't sure if I should start here. I will start there first next time.Thanks again bitsmed.
SSRS is just a reporting tool. The query is still getting executed at DB2 end so it has to be as per DB2 syntax which will have slight variations from MS SQL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
dotsofcolor
Starting Member
12 Posts |
Posted - 2013-05-23 : 09:10:03
|
| Thanks visakh16 for the feedback and clarification. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-23 : 09:57:46
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Getting Monday's Date From Any Date Of The Week