CODEWITHSUNDEEP
sql-serverdatedatetime
Paolo Baeli
Paolo Baeli

Reputation: 11

Convert string date to datetime on sql server

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!

Upvotes: 0

Views: 239

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8101

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

Upvotes: 1

Related Questions