0

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.

Harry
  • 2,636
  • 1
  • 17
  • 29
  • 1
    `cast(replace(carcass_date, '.', '') as datetime)` – ZLK May 12 '17 at 04:18
  • wow.. really.. that simple! Thank you so much.I can accept this as the answer if you make it an answer rather than a comment.. – Harry May 12 '17 at 04:23

2 Answers2

1

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)
ZLK
  • 2,864
  • 1
  • 10
  • 7
  • I just tested on my system and a value of `'05/11/2017 9:18:46 a.m.'` returns 11th May which is the incorrect value. – Nick.Mc May 12 '17 at 04:49
  • @Nick.McDermaid That would depend on your DATEFORMAT settings... e.g. DMY vs MDY. 05/11/2017 _is_ the 11th of May if you're using MDY... – ZLK May 12 '17 at 05:16
  • It appears the only solution not dependent on an external config is `convert` – Nick.Mc May 12 '17 at 05:24
  • @Nick.McDermaid Yes, but that also requires already knowing the format your dates are going to be in. e.g. If they are being entered as MM/DD/YYYY (101), then converting them to 103 is going to give you the incorrect dates. Casting with your default DATEFORMAT seems like the correct option to me, but I can see why you might think differently. – ZLK May 12 '17 at 05:26
  • But we _do_ already know what the format it's in. If we didn't, cast still wouldn't be any better! Just trying to promote defensive coding here. Why suggest a method that gives you a different result depending on server config (cast) when you could use a method that produces the same result every time regardless (convert) – Nick.Mc May 12 '17 at 06:55
  • @Nick.McDermaid The point is that yes, it gives a different result depending on the configuration, but that isn't necessarily a bad thing because the default configuration more than likely matches the input. On the other hand, if I convert the date with the third parameter 103, then I've guaranteed that I get the wrong date if my strings are in MM/DD/YYYY format. I think it's wiser to assume the input matches the default configuration of a server than to assume it will always be a particular way, but as I said, I can see why you might think differently. – ZLK May 12 '17 at 07:04
  • LOL, I respectfully, but completely disagree. :) – Nick.Mc May 12 '17 at 08:01
1

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?

Community
  • 1
  • 1
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Thanks Nick. As you have suggested, I have used the convert(datetime.. option for certainty. – Harry May 12 '17 at 18:48