June 9, 2009 at 12:47 pm
I have two 'Case' Statements.
, CASE
When vtbl_Job_Descript_Master.START <> 0 then Convert(int,Substring(LTrim(Str(vtbl_Job_Descript_Master.START)),5,2))
When ISNULL(MAX(vtbl_TCH_First_Labor.First_Week_Ending), 0) <> 0 THEN Convert(int,Substring(LTrim(Str(vtbl_TCH_First_Labor.First_Week_Ending)),5,2))
ELSE Convert(int,Substring(LTrim(Str(vtbl_Job_Descript_Master.Closed)),5,2))
END
AS Job_Start_Month
, CASE
When vtbl_Job_Descript_Master.START <> 0 then Round((vtbl_Job_Descript_Master.START / 10000),0,1)
When ISNULL(MAX(vtbl_TCH_First_Labor.First_Week_Ending), 0) <> 0 THEN Round((vtbl_TCH_First_Labor.First_Week_Ending / 10000),0,1)
ELSE Round((vtbl_Job_Descript_Master.Closed / 10000),0,1)
END
AS Job_Start_Year
However I would like to have
Case
When Job_Start_Month =< 3 Then Job_Start_Year - 1
Else Job_Start_Year
End
As Job_Start_Fiscal_Year
[/code]
Can someone help me with this?
Thank you
June 9, 2009 at 1:22 pm
What are the data types for vtbl_Job_Descript_Master.START and vtbl_Job_Descript_Master.CLOSED?
If START is a datetime, a simple solution would be YEAR(dateadd(mm, -3, vtbl_Job_Descript_Master.START))
June 9, 2009 at 1:33 pm
Unfortunately Start and Close are in YYYYMMDD format.
Which is why I convert them to a string to strip out what I need.
June 9, 2009 at 1:42 pm
Ken (6/9/2009)
Unfortunately Start and Close are in YYYYMMDD format.Which is why I convert them to a string to strip out what I need.
Guess what, I am pretty sure that valid dates in that format will implicitly convert to a datetime value and allow you to use the formula I provided. Just tested on one of my development servers and it worked.
June 9, 2009 at 1:58 pm
Okay, I know this should be easy since I figured out the convert in the first place.
But... I can't seem to get it to work.
What is the proper syntax for converting 20090601 to 6/1/2009?
I am getting "Arithmetic overflow error converting expression to data type smalldatetime."
I am using
Case
When Start > 0 Then Convert(smalldatetime,Convert(int,Start))
Else 0
End
As Job_Start
Thanks,
June 9, 2009 at 2:01 pm
I also tried
Case
When Start > 0 Then Convert(smalldatetime,Start)
Else 0
End
As Job_Start
June 9, 2009 at 2:20 pm
June 9, 2009 at 2:24 pm
I tried and tried using different versions of what Lynn provided, using with and without Convert.
I kept getting errors. However I just tried
YEAR(dateadd(mm, -3, Convert(smalldatetime,str(START)))) As Job_Start
and it worked.
Yeah!!!
I still don't know why it wouldn't work using just "START"
Any ideas?
If not that it's working... Thank you for all of your help.
June 9, 2009 at 2:29 pm
June 9, 2009 at 2:47 pm
I'm guessing it is an INT field based on what I have read so far.
June 9, 2009 at 2:48 pm
In the vtbl_TCH_First_Labor table it could be either a Null which I convert to 0 or the YYYYMMDD format.
In the other table it should always be in the YYYYMMDD format.
However, I should note that this uses an OpenSource query from an IBM iSeries.
This may not be specific as you are asking.
June 9, 2009 at 2:50 pm
I suspect I am getting an INT from the iSeries.
June 9, 2009 at 3:23 pm
since it is an INT datatype a direct conversion is not possible be sql server does a conversion from into to datetime as the value of days since 1900-01-01.
--today's date june 9,2009
select CONVERT(datetime,39971)
so the way you have done it by converting to string then datetime would be the correct course. You had asked how to get it to mm/dd/yyyy format. It would be accomplished like so
convert(varchar(10),convert(smalldatetime,str(start)),101)
the following link explains the 101 code for datetime in the remarks section, date and time styles heading
June 10, 2009 at 6:58 am
That really helped.
Thanks to both for your help.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply