CODEWITHSUNDEEP
sql-serverdatedatetime
Harry
Harry

Reputation: 2941

SQL Server convert ' dd/MM/YYYY H:mm:ss p.m..' to datetime

I am importing a text column into a SQL Server 2008 database.

I have a field 'carcassdate' in the following format

'11/05/2017 9:18:46 a.m.'

'10/05/2017 1:08:27 p.m.'

etc. How can I convert this into a proper SQL Server 2008 datetime (24 hour) column please?

I can't seem to find a proper solution for this.

My desired result is

'11/05/2017 9:18:46 a.m.' converted to 2017-05-11 09:18:46.000

'10/05/2017 1:08:27 p.m.' converted to 2017-05-10 13:08:27.000

Thanks in advance.

What I have tried so far: strip the date part of the column

select  convert(date, convert(varchar(10),carcass_date)) as carcassdate

That's the easy part..But I'm struggling with the time part.

Thanks for your help in advance.

Upvotes: 0

Views: 1402

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19194

When it comes to date conversion, I suggest that you always be explicit.

Any kind of programming that is not explicit about date formats invites bugs.

I suggest you Never use cast as there is no explicit format. Even though it always assumes ISO format, I've never found any info about how it decides other formats (including your which is not ISO)

Never use convert without a format number.

I suggest you instead use convert with a format number. On my system, this returns two different dates. Cast returns the wrong one.

DECLARE @D AS VARCHAR(100) = '05/11/2017 9:18:46 a.m.'

SELECT CONVERT(DATETIME, REPLACE(@D, '.', ''), 103) as converted

SELECT CAST(REPLACE(@D, '.', '') AS DATETIME) as casted

Number 103 is defined as dd/mm/yyyy format. At least this way you have some degree of self documentation - that's the format you expect

Later versions of SQL have parse but it uses cultures, not format strings.

Perhaps read this

https://www.simple-talk.com/sql/t-sql-programming/how-to-get-sql-server-dates-and-times-horribly-wrong/

There's also a non-decisive discussion here:

datetime Cast or Convert?

Upvotes: 1

ZLK
ZLK

Reputation: 2874

The issue is with the periods in the string. By removing them, you can cast the string as a datetime. e.g.:

SELECT CAST(REPLACE(carcass_date, '.', '') AS DATETIME)

Upvotes: 1

Related Questions