November 1, 2005 at 3:42 am
hi all
I have a stored procedure that gets date from a particular field using the DATEPART function. However, somewhere in between it has to do an update which is why it adds hours, days or months to the function. Here is the code
SELECT @var_NewNextRunDate=(CAST(DATEPART(YYYY,@DT) AS CHAR(4)) + '/'
+ RIGHT(CAST(100+DATEPART(MM,@DT) AS CHAR(3)),2) + '/'
+ RIGHT(CAST(100+DATEPART(DD,@DT) AS CHAR(3)),2) + ' '
+ RIGHT(CAST(100+DATEPART(HH,@DT) + @var_Frequency AS CHAR(3)),2) + ':'
+ RIGHT(CAST(DATEPART(MI,@DT) AS CHAR(2)),2) + ':'
+ RIGHT(CAST(DATEPART(SS,@DT) AS CHAR(2)),2)+ ':'
+ RIGHT(CAST(DATEPART(MS,@DT) AS CHAR(3)),3) )
Now the problem is that when there a change in day the hours get stuck because it simply adds the hours so if it is 23:40 and it adds 3 it becomes 26:40 which makes no sense. Can someone please help me find a solution to this? I have the same situation with days, and months as well.
Thank you.
November 1, 2005 at 4:45 am
DatePart returns an integer so the addition is correct, but not desired! For an update on as datetime column I would also return that column and use a DataAdd function to get the desired results and update the column.
DateAdd(hh, @var_Frequency , datetimecolumn)
If this does not help please post more of the stored procedure.
November 1, 2005 at 5:53 am
Yes - always easiest to use the actual date functions to work with dates rather than, possible more intuitively, to work with dates by converting to to their parts, performing manipulation and then converting back..
Dates are actually stored as a floating point value - the integral part being the number of days since 1 Jan 1900 and the fractional part representing a fraction of a single day. You can simply increment days by adding a whole number, or increment a part of a day (eg, 1 hour) by adding a fraction (eg 1/24). The DateAdd function gives a convenient way to do this. If you think of dates in this fashion, you'll find it a lot easier.
To then format the date in a convenient way, look at the "convert" function in Books Online, or you can use datepart to extract the parts of the modified date and format as you see fit (although many would say the formatting should be left to the app displaying the data).
November 1, 2005 at 8:47 am
you could use a modulus 24 if need be with the math to "round" it back down, but you'd need to handle the math for the carry yourself as well.
Use the date functions as suggested above.
November 2, 2005 at 10:46 pm
Thanks a bunch Kory Becker. It worked out perfect with just one line of code. Thanks to others as well for further insights.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply