DATETIME losing hh:mm when changing CREATE TABLE to SELECT INTO

I am currently migrating all of my company's reports into Splunk Data Labs input for ingestion. The reports create temp tables using the CREATE TABLE format, which is incompatible with Splunk, however, SELECT INTO format works just fine.

The error that I am getting however when changing to the SELECT INTO format, is the DATETIME variable which should be MM/DD/YYYY hh:mm format loses the hh:mm end, and instead shows MM/DD/YYYY MM/DD/YYYY:

Original SQL:

CREATE TABLE #Stats#(date_slice DATETIME NULL, raw_value REAL NULL)
INSERT INTO #Stats#
    SELECT CONVERT(CHAR(11), data_datetime, 111) + ' ' +
        CASE WHEN DATEPART(MINUTE, data_datetime) < 30 THEN
        RIGHT('0' + LTRIM(str(DATEPART(hour, data_datetime))), 2) + ':00' ELSE
        RIGHT('0' + LTRIM(str(DATEPART(hour, data_datetime))), 2) + ':30' END AS date_hour
        ,SUM(ship_qty) AS moves
    FROM #tmpAllData
    GROUP BY CONVERT(CHAR(11), data_datetime, 111) + ' ' +
        case when DATEPART(minute, data_datetime) < 30 THEN
        RIGHT('0' + LTRIM(str(DATEPART(hour, data_datetime))), 2) + ':00' ELSE
        RIGHT('0' + LTRIM(str(DATEPART(hour, data_datetime))), 2) + ':30' END
    ORDER BY 1

Modified SQL:

--CREATE TABLE #Stats#(date_slice DATETIME NULL, raw_value REAL NULL)
SELECT CONVERT(CHAR(11), data_datetime, 111) + ' ' +
    CASE WHEN DATEPART(MINUTE, data_datetime) < 30 THEN
    RIGHT('0' + LTRIM(str(DATEPART(hour, data_datetime))), 2) + ':00' ELSE
    RIGHT('0' + LTRIM(str(DATEPART(hour, data_datetime))), 2) + ':30' END date_slice
    ,SUM(ship_qty) raw_value
INTO #Stats#
FROM #tmpAllData
GROUP BY CONVERT(CHAR(11), data_datetime, 111) + ' ' +
    case when DATEPART(minute, data_datetime) < 30 THEN
    RIGHT('0' + LTRIM(str(DATEPART(hour, data_datetime))), 2) + ':00' ELSE
    RIGHT('0' + LTRIM(str(DATEPART(hour, data_datetime))), 2) + ':30' END
ORDER BY 1

Original Output: "07/12/2018 10:00:00 "
Modified Output: "2018/07/12 2018/07/12"

Solution

You second statement is creating the temporary table #Stats without any preset column definitions. It instead creates the columns based on the return data type of the SELECT

This means that the SQL Server is not reading the output in question as a DATETIME but instead as a STR.

I would try to use a CONVERT in your modified statement to see if you get different functionality.

It sounds like this question is mostly for your curiosity so I will add that the original statement is the standard way to accomplish what you've outlined.

This is because SELECT... INTO statements are harder to read and revise for new users and because they can lead to some unexpected functionality as you have displayed above.