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 your post, don't add further information to your post via the comments. — Thom A
– Thom A ♦, Commented Apr 12, 2018 at 10:02
Is this as simple as CONVERT(VARCHAR(10), Col1) + CONVERT(VARCHAR(10), Col2) if not then what do you need? — MJH
– MJH, Commented Apr 12, 2018 at 10:04
Best advice: if possible, don't store these as strings in the database in the first place. — DavidG
– DavidG, Commented Apr 12, 2018 at 10:04
Also, which year are these dates for? Always the current year? Good luck processing these over new year... — DavidG
– DavidG, Commented Apr 12, 2018 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
– Thom A ♦, Commented Apr 12, 2018 at 10:21

Eric Brandt · Accepted Answer · 2018-04-12 11:29:42Z

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 |
+-------------------------+

Collectives™ on Stack Overflow

Convert string date to datetime on sql server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related