question

Fatherjack avatar image
Fatherjack asked

Convert DateTime to Numeric

OK, I have gone 'snow blind', I cant get the time part of a SQL 2005 DateTime column into a Numeric column in a different database. Its a 'literal' transfer so take what the time looks like in TableA and insert it into TableB so it looks the same, except for there being a . instead of a : in the middle (ie 10:30 becomes 10.30, 23:49 becomes 23.49)

I have tried so many different combinations of DATEPART and CAST that I am losing the will to live!

In the UPDATE statement I simply want

Update TableB
SET TimeRecorded = CAST(DATEPART(..(TableATimeColumn)..)..) -- whatever it needs to be

Any advice welcomed.

t-sqldate-functionscast-convert
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
Forgot to mention, the database is not ours to alter, otherwise I wouldnt store a time in a NUMERIC column.
0 Likes 0 ·

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered

For example

SELECT DATEPART(hour, GETDATE()) + DATEPART(minute, GETDATE()) / 100.0

to meet your problem

Update TableB
SET TimeRecorded = DATEPART(hour, TableATimeColumn) + DATEPART(minute, TableATimeColumn) / 100.0
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
ah.. sometime you are focused on something and didn't see so simple solution. I know this from my own experience.. Thinking about some solution for serious time and after it's done in some "heavy" way, you are working on something else and a very simple solution for previous problem comes from nothing. :-)
3 Likes 3 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
I have had a lot of that in my time... I think the best was when I was coming up with an extravagant way to round a float to the nearest integer, and a colleague suggested I just added .5 and took the integer portion...
3 Likes 3 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.