February 2, 2023 at 11:35 pm
Definition of stored procedure
create procedure [dbo].[smGetMinDate] @O_dMinDate datetime = NULL
output as declare @dDefaultMinDate datetime
select @dDefaultMinDate = GETDATE()
select @dDefaultMinDate = DATEADD(mm,-(DATEPART(mm,@dDefaultMinDate))+1,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(dd,-(DATEPART(dd,@dDefaultMinDate))+1,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(yy,-(DATEPART(yy,@dDefaultMinDate))+1900,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(hh,-(DATEPART(hh,@dDefaultMinDate))+0,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(mi,-(DATEPART(mi,@dDefaultMinDate))+0,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(ss,-(DATEPART(ss,@dDefaultMinDate))+0,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(ms,-(DATEPART(ms,@dDefaultMinDate))+0,@dDefaultMinDate)
select @O_dMinDate = @dDefaultMinDate
return
stored procedure call (Never seen output while (@tLoop is NULL) ----what does this mean?
exec @iStatus = smGetMinDate @dMinDate output while (@tLoop is NULL)
GO
February 3, 2023 at 6:59 am
This proc takes the current datetime and does a whole lot of unnecessary calculations to reduce it to 01-JAN-1900
You execute this proc as follows
DECLARE @ResultDate datetime;
EXEC dbo.smGetMinDate @O_dMinDate = @ResultDate OUT;
SELECT ResultDate = @ResultDate;
There are many ways to skin a cat, but here are 2 alternative ways to get the value of 01-JAN-1900.
SELECT SameResult1 = CONVERT(datetime, 0)
, SameResult2 = CONVERT(datetime, '1900-01-01');
February 3, 2023 at 11:52 am
Here is a list of Some Common Date Routines by Lynn Pettis
If you are using SQL2022, you can use the DATETRUNC function for these kind of calculations.
February 3, 2023 at 9:08 pm
What are you trying to do? Why do you like screwed up presentation formats for your data? The whole idea of client/server architecture from the 1970s is that data is passed from one layer to another and worked on by his current layer. You're doing data formatting in the database retrieval layer of your architecture. And you're not even using a decent format! This crap should have been done in a presentation layer or a front-end program, probably COBOL from the look of it.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 4, 2023 at 6:25 am
What are you trying to do? Why do you like screwed up presentation formats for your data? The whole idea of client/server architecture from the 1970s is that data is passed from one layer to another and worked on by his current layer. You're doing data formatting in the database retrieval layer of your architecture. And you're not even using a decent format! This crap should have been done in a presentation layer or a front-end program, probably COBOL from the look of it.
Read the original question instead of badmouthing the OP. It's not his code. He simply wanted to know what the code was doing. Speaking of crap, look at your own post. All it offers is badmouthing the Op for something that he didn't do because you can't get off your high horse long enough to actually see what's going on.
Your post is a new low even for you, Joe.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 5, 2023 at 12:11 am
Definition of stored procedure
create procedure [dbo].[smGetMinDate] @O_dMinDate datetime = NULL
output as declare @dDefaultMinDate datetime
select @dDefaultMinDate = GETDATE()
select @dDefaultMinDate = DATEADD(mm,-(DATEPART(mm,@dDefaultMinDate))+1,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(dd,-(DATEPART(dd,@dDefaultMinDate))+1,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(yy,-(DATEPART(yy,@dDefaultMinDate))+1900,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(hh,-(DATEPART(hh,@dDefaultMinDate))+0,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(mi,-(DATEPART(mi,@dDefaultMinDate))+0,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(ss,-(DATEPART(ss,@dDefaultMinDate))+0,@dDefaultMinDate)
select @dDefaultMinDate = DATEADD(ms,-(DATEPART(ms,@dDefaultMinDate))+0,@dDefaultMinDate)
select @O_dMinDate = @dDefaultMinDate
returnstored procedure call (Never seen output while (@tLoop is NULL) ----what does this mean?
exec @iStatus = smGetMinDate @dMinDate output while (@tLoop is NULL)
GO
I just tried that stored procedure. It doesn't seem to matter what you provide as the value for the input variable... it seems like it always returns 1900-01-01 00:00:00.000. I might have done something incorrectly but it sure doesn't seem to be doing anything correctly.
If you look at the code, it subtracts the value of the month and day, each resulting in 0, and then adds 1 back to it. No matter what the current date is (the passed in date doesn't even come into the picture, null or not), the month and day returned will always be 1 for each.
Similar happens with the year. They subtract the year from the date, resulting in 0 and then add in 1900 and so the year returned will ALWAYS be 1900.
The other parts for the time all go through the same thing except nothing is added back in and so all positions for time will always be 0.
This proc is like the old joke of asking someone how to keep an idiot in suspense. Almost every answers, "I dunno... How?" and you simply don't answer. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply