-2

We receive a csv file that has a column in this date format -- Wed Oct 14 08:00:00 CDT 2020, along with a column that has a count for each date/time

I am using an SSIS package to grab the file and import this data into a sql table, then I can format it the way I need to and then actually export the data in the format needed.

If there is a way to do this all within one SSIS package I am all ears but currently I am working on just getting the data into SQL and converted to the right format so that I can export it.

I need to get that file and convert that date format and split it up into two separate columns

One column will be just the date in this format 2020-10-14 00:00:00.000

One column will be just the time in this format 08:00:00.0000000

Updated to change the dates to match so it's not as confusing and also the error I am receiving when running the suggested code below.

Image of Error I'm recieving

Image of table with the data I am trying to convert

Image of table attributes

Screenshot of my screen when running a select * from the table I am pulling the data that I need converted

Screenshot of the error I receive when running the query by Aaron.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Lyndsey Cupit
  • 3
  • 1
  • 3
  • You'll have to write code to parse this custom and frankly *quite* unusual string. What is CDT? Cuba Daylight Time? The timezone [abbreviations](https://en.wikipedia.org/wiki/List_of_time_zone_abbreviations) are ambiguous and not standardized. There are at least 3 ISTs and BSTs too – Panagiotis Kanavos Oct 16 '20 at 18:19
  • if you wrote a C# script transformation you could parse every element *except* the timezone abbreviation – Panagiotis Kanavos Oct 16 '20 at 18:20
  • You didn't implement my query correctly, that's all. You needed to apply the original `TRY_CONVERT` to the table, then extract date and time from that. In your implementation you applied `TRY_CONVERT` to the variable, but then still tried to extract date and time from the unaltered values in the original table. That was my fault - I didn't include the table in the solution because you didn't originally provide table/column names. Please see my updated answer. – Aaron Bertrand Oct 16 '20 at 19:22
  • THAT WORKED! YOU ARE A GOD SEND!!!! THANK YOU SO SO SO MUCH! @AaronBertrand – Lyndsey Cupit Oct 16 '20 at 19:25
  • @AaronBertrand I hate to bother you AGAIN.... but how can I also get the 'Covers' column to pull with the new formatted fields? – Lyndsey Cupit Oct 16 '20 at 19:45
  • Just select any other columns you want in `src` then you can reference them outside. – Aaron Bertrand Oct 16 '20 at 19:47
  • @AaronBertrand again, you're the best! Thank you! – Lyndsey Cupit Oct 16 '20 at 19:48

2 Answers2

1

If this is the format it will always be in, and timezone is irrelevant, you can first try to convert it to a datetime, then you can extract the parts from that.

SET LANGUAGE us_english; -- important because not all languages understand "Oct"

;WITH src AS 
(
  SELECT dt = TRY_CONVERT(datetime, RIGHT(OpenedDateTime ,4) 
              + SUBSTRING(OpenedDatetime, 4, 16))
         --, other columns...    
    FROM [dbo].[VIRTUALROSTERIMPORT_Res_Import]
)
SELECT OpenedDateTime = CONVERT(datetime, CONVERT(date, dt)), 
       OnHour = CONVERT(time, dt)
       --, other columns... 
FROM src;

Results:

OpenedDateTime   OnHour
--------------   ----------------
2020-10-14       08:00:00.0000000

If you need to shift from one timezone to another timezone, that's a different problem.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • @PanagiotisKanavos That's why I asked for clarification about whether the timezone is even relevant. – Aaron Bertrand Oct 16 '20 at 18:26
  • You assume the OP wants the local time? There are two CDTs with 1 hour difference – Panagiotis Kanavos Oct 16 '20 at 18:27
  • @PanagiotisKanavos I've asked for clarification. And also stated my assumption. The dates weren't right either (how do you get Jan 22 from Oct 14), so yes, I've assumed that the timezone is in the source but isn't relevant (the OP never stated the destination timezone, are you just assuming it's a different timezone because they went from 8:00 to 6:00?). – Aaron Bertrand Oct 16 '20 at 18:27
0

I was just showing the date formats, don't look so into the actual date examples I used. The time zone is irrelevant I just need the formats changed. When I used The code Aaron suggested I got a conversion error: I'm assuming its because the columns are varchar in the table, but I cant get the dates to load as date formats bc SSIS keeps giving me truncated errors-- so I have to load it as varchar.

Below is the code I was running, I tweaked it to use the column and table names I am using.

SET LANGUAGE us_english; -- important because not all languages understand "Oct"

DECLARE @foo varchar(36) = 'Wed Oct 14 08:00:00 CDT 2020';

;WITH src(d) AS 
(
  SELECT TRY_CONVERT(datetime, RIGHT(@foo,4) + SUBSTRING(@foo, 4, 16))
)
SELECT OpenedDateTime = CONVERT(datetime, CONVERT(date, OpenedDateTime)), 
       onhour = CONVERT(time, OpenedDateTime) 
FROM [dbo].[VIRTUALROSTERIMPORT_Res_Import];
Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
Lyndsey Cupit
  • 3
  • 1
  • 3
  • This is the column that is in this format : Wed Oct 14 08:00:00 CDT 2020 – Lyndsey Cupit Oct 16 '20 at 18:47
  • But that value works. Show another value that doesn't work. – Aaron Bertrand Oct 16 '20 at 18:53
  • @AaronBertrand I just edited my entry to include two screen shots that may be helpful! – Lyndsey Cupit Oct 16 '20 at 18:56
  • I don't think any of the values in the screenshot are the ones that are making this fail. Try `SELECT TOP (1) OpenedDateTime FROM dbo.VIRTUALROSTERIMPORT_Res_Import WHERE ISDATE(RIGHT(OpenedDateTime,4) + SUBSTRING(OpenedDateTime, 4, 16)) = 0` – Aaron Bertrand Oct 16 '20 at 19:02
  • That came back blank with no results! @AaronBertrand – Lyndsey Cupit Oct 16 '20 at 19:02
  • You applied `TRY_CONVERT()` to the variable here, but then you ignored that output and still tried to extract the date and time from the original, poorly formatted strings in the table. The whole point of `src` was to transform those values to something `CONVERT` could understand. You brought the table into the query but you put it in the wrong place. – Aaron Bertrand Oct 16 '20 at 19:25