How to store hour datepart in SQL in table

Question

I have a table with 2 columns: Customer_ID, which is a string, identifying each client and Time_id: a string with 14 characters, identifying timestamp of a transaction. Example:

Customer_id; Time_id
12345; 20140703144504

I want to be able to use datediff in hours datepart, but I can´t seem to be able to convert time_id properly. I use the following query:

update transation_table
set time_id= (
convert(timestamp, time_id)
)

It works, but removes hours datepart, which is what I need. For day datepart I can do it, converting to datetime. How can I keep in the table the hh?

edit: I´m running MS SQL Server 2014.

best regards

what kind of timestamp is this? where do you generate it? Ah now that I look at it, that is no timestamp! ;) That looks like just a normal datetime value 2014-07-03 14:45:04 where you just stripped off the `-` and `:` — DrCopyPaste, Jul 03 '14 at 14:03
Why are you not storing this data in a column of an appropriate type in the first place? — Damien_The_Unbeliever, Jul 03 '14 at 14:06
You´re right, bad use of words!:) I don´t know how it was generated. That´s correct, it is a datetime value, but I need it to have the formal definitions of time to use datediff, If i´m not mistaken. Like I said, for the day datepart it works fine, and datediff too. When I used it for day datepart I stripped it from hh-mm-ss, using left command. — Manuel Silva, Jul 03 '14 at 14:12
since the format you have those "timestamps" saved in probably wont change in the next 7000 years you can use `STUFF(STUFF(STUFF(STUFF(STUFF('20140703144504', 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0, ':')` to transform it into a standardized date format (like `2014-07-03 14:45:04`) that can then easily be converted or worked on via `DATEPART` to determine hour part for example. — DrCopyPaste, Jul 03 '14 at 14:24
I see. Thanks for helping me out. SpectralGhost and beargle´s answers are both very good too. — Manuel Silva, Jul 03 '14 at 14:31

UnhandledExcepSean · Accepted Answer · 2014-07-03T14:37:00.280

1

Using the convert and string concatenation below, you can use DATEPART on the resulting value.

DECLARE @tmp TABLE(
    Customer_id VARCHAR(50),
    Time_id VARCHAR(50)
)

INSERT INTO @tmp
    SELECT '12345','20140703144504'

select
    *,CONVERT(DATETIME,
        SUBSTRING(Time_id,5,2) + '/' +
        SUBSTRING(Time_id,7,2) + '/' +
        SUBSTRING(Time_id,1,4) + ' ' +
        SUBSTRING(Time_id,9,2) + ':' +
        SUBSTRING(Time_id,11,2) + ':' +
        SUBSTRING(Time_id,13,2)
        ,101
    )
from @tmp

edited Jul 03 '14 at 14:37

answered Jul 03 '14 at 14:13

UnhandledExcepSean

12,504
2
35
51

1

If you're going to mangle a string into a format that can be converted into a `datetime`, please, at the very least *either* mangle it into an unambiguous format, *or* at least specify a *style* parameter in the `CONVERT` call. This currently does neither and may end up generating a datetime that is either the 7th of March or the 3rd of July. – Damien_The_Unbeliever Jul 03 '14 at 14:31

Bryan · Answer 2 · 2014-07-03T14:27:40.270

Use FORMAT to get a string representation of the value in a supported format (ODBC canonical in the Date and Time styles chart), then use TRY_CONVERT to return an actual datetime value:

SELECT TRY_CONVERT(DATETIME,
                   FORMAT(CAST('20140703144504' AS BIGINT),
                          '####-##-## ##:##:##'),
                   120);

This requires SQL Server 2012+.

As mentioned elsewhere, the data should be stored in a single datetime2 column, or paired date and time columns. The above functions can be used to help convert existing data to the new column(s).

How to store hour datepart in SQL in table

2 Answers2