I messed up when importing my data and now I'm stuck with two weird columns:
1. Date column (INT type) looks like this: 20131231
2. Time column (datetime type) shows up as: 1899-12-30 13:30:00.000
How can I combine these into one proper DateTime column? I want to keep the actual dates and times from my data. Is there a way to do this in SQL? I'm not sure how to handle the different formats and types.
Any help would be great. I'm using SQL Server if that matters. Thanks!
yo, that’s a tricky one! have u looked into DATETIMEFROMPARTS? it might work like this:
DATETIMEFROMPARTS(LEFT(date_column,4), SUBSTRING(date_column,5,2), RIGHT(date_column,2),
DATEPART(HOUR,time_column), DATEPART(MINUTE,time_column), 0, 0)
give it a shot and lmk how it goes!
Your situation is indeed challenging, but not insurmountable. A potential solution involves using the DATEADD function in combination with CAST. Here’s an approach you might consider:
DATEADD(HOUR, DATEPART(HOUR, time_column),
DATEADD(MINUTE, DATEPART(MINUTE, time_column),
CAST(CAST(date_column AS VARCHAR(8)) AS DATETIME)))
This method first converts your date column to a proper date, then adds the hours and minutes from your time column. It avoids the year 1899 issue by focusing on the time components.
Remember to thoroughly test this solution with your specific dataset to ensure accuracy. If you encounter any issues, you might need to adjust for potential data anomalies or time zone considerations.
ooh, interesting data puzzle! have you tried using CONVERT or CAST functions to manipulate those columns? maybe something like:
CONVERT(datetime, CAST(date_column AS VARCHAR(8)) + ’ ’ + CONVERT(VARCHAR(8), time_column, 108))
just a thought! what other approaches have u considered? curious to hear if anyone else has tackled something similar