Convert string date to datetime on sql server

Question

i've have something like this:

table .

I need to concatenate and convert the DATA and ORA fields into one because I'll insert those in another table with just one field. My problem is to convert them 'cause I've not found any format good for making it. Also the customer uses "Italian month" like in the photo... Apr it's "Aprile" (April)

Does someone have a possible solution? I can't actually modify the format of the two fields unfortunately.

EDIT: the table fields are VARCHAR(MAX), the point is i need to make an insert into another table where the "date" field is in datetime format, and the year it's supposed to be always the current one

EDIT 2: i create and drop this small table every time, and data is brought in by a bulk insert from a .csv

EDIT 3: i'm sorry but i'ts my first question =)...btw the output should be like this table here with the "DATA" in datetime format

EDIT 4: DDL: create table notaiTESTCSV( NUMERO_FINANZIAMENTO varchar(MAX), DATA varchar(MAX), ORA varchar(MAX), )

EDIT 5: this is how i take data from csv: bulk insert notaiTESTCSV from 'path\SPEDIZIONE NOTAI.csv' with (firstrow = 2,fieldterminator = ';', rowterminator =' ') The customer uses "Italian month" like in the photo

PS: sorry for my bad English it's not my first language

Thank you in advance!

[Edit](https://stackoverflow.com/review/suggested-edits/19411426) your post, don't add further information to your post via the comments. — Thom A, Apr 12 '18 at 10:02
Is this as simple as `CONVERT(VARCHAR(10), Col1) + CONVERT(VARCHAR(10), Col2)` if not then what do you need? — MJH, Apr 12 '18 at 10:04
Best advice: if possible, don't store these as strings in the database in the first place. — DavidG, Apr 12 '18 at 10:04
Also, which year are these dates for? Always the current year? Good luck processing these over new year... — DavidG, Apr 12 '18 at 10:04
Posting the DDL of your table would be useful here and your expected results. I'm not sure if `ORA` is a literal string with the value `'9,00'`, or a `decimal` (`9.00`) but using a different display format to the norm. — Thom A, Apr 12 '18 at 10:21
Perhaps you could edit your question to include DDL and include a sample of your required output? https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — MJH, Apr 12 '18 at 10:36
@PaoloBaeli: yes, but do they all end in `,00`, that is, are all the times in whole hours? Otherwise, a time like `9:30` might be represented as `9,50` (nine and a half hours) or `9,30` (whole minutes after the comma). — Jeroen Mostert, Apr 12 '18 at 10:46
@JeroenMostert they can end also with other numbers not only ,00 — Paolo Baeli, Apr 12 '18 at 10:54
Please, @PaoloBaeli , have a look at the link MJH provided. post the data as DDL and as a `INSERT` statement; you're pictures aren't giving us the answers to our questions. We also still need those expected results. Help us help you; if you can't/won't help us by answering our questions about your problem, how can we hope to help you? — Thom A, Apr 12 '18 at 10:54
If they can end with other values than `,00` then post other examples (that don't end in `,00`) as well when you post your DDL, `INSERT` statement and expected results. — Thom A, Apr 12 '18 at 10:54
Is `ORA` the year? Are you trying to concatenate a `Day-Month` string with a `Year` string and convert it to a date? — MJH, Apr 12 '18 at 11:40

score 1 · Accepted Answer · answered Apr 12 '18 at 11:29

SQL Server is remarkably robust in the ways it can manage datetime data. This gets ugly by the end, so I tried to break it down some to show what it's doing in steps.

Here's what each piece does by itself:

DECLARE @data varchar(100) = '19-apr',
        @ora varchar(100) = '9,00',
        @dt datetime,
        @tm datetime;

--The date component
SET @data = CONCAT(@data,'-',CAST(YEAR(GETDATE()) AS VARCHAR(4)));
SET @dt = CAST(@data as DATETIME);

--The time component
SET @ora = CONCAT(REPLACE(@ora,',',':'),':00');
SET @tm = CAST(@ora as DATETIME);

Then a little help from our friends, showing that math works: How to combine date from one field with time from another field - MS SQL Server

SELECT @dt + @tm AS [MathWorks];

Results:

+-------------------------+
|        MathWorks        |
+-------------------------+
| 2018-04-19 09:00:00.000 |
+-------------------------+

Bringing it all into one statement

DECLARE @data varchar(100) = '19-apr',
        @ora varchar(100) = '9,00';

SELECT CAST(CONCAT(@data,'-',CAST(YEAR(GETDATE()) AS VARCHAR(4))) as DATETIME) 
       + 
       CAST(CONCAT(REPLACE(@ora,',',':'),':00') as DATETIME) AS [CombinedDateTime]

Results:

+-------------------------+
|    CombinedDateTime     |
+-------------------------+
| 2018-04-19 09:00:00.000 |
+-------------------------+

Convert string date to datetime on sql server

1 Answers1