Problem with handling dates

Question

Problem with handling dates

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kory Becker Hall of Fame Points: 3585 More actions · Answer 1

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.

Ian Yates SSCoach Points: 19738 More actions · Answer 2

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).

Steve Jones - SSC Editor SSC Guru Points: 739541 More actions · Answer 3

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.

Oscar-223764 SSC Rookie Points: 27 More actions · Answer 4

Thanks a bunch Kory Becker. It worked out perfect with just one line of code. Thanks to others as well for further insights.