-1

I made the mistake of using varchar to store my data that represents date/time. Now I'm trying to convert my column of text data, stored as a varchar, like so:

2020-11-25T14:22:41.3539327Z

Into a column that stores a datetime datatype.

Patrick
  • 39
  • 1
  • 6
  • Luckily, this is an unambiguous format so `cast(field as datetime2)` or `cast(field as datetimeoffset)` will work. I suspect if you change the type from SSMS or use `ALTER TABLE` the data will be converted without problem – Panagiotis Kanavos Dec 15 '20 at 19:34
  • @DaleK No, I don't believe so. It may partially, but it appears the Z in my datetime will cause problems. – Patrick Dec 15 '20 at 19:52
  • @PanagiotisKanavos I believe the same problem occurs with your answer. In fact I tried to use ALTER TABLE before posting here. Is there a simple way to omit the last character from my cast? – Patrick Dec 15 '20 at 19:54
  • @FastQ sure, but the technique is the same. If you were to check the [official docs](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15), which should always be your first port of call, you will find the correct format to convert your date. – Dale K Dec 15 '20 at 19:54
  • What `same problem`? You only asked how to convert the data. `cast` works. The UTC indicator isn't a problem at all and shouldn't be removed – Panagiotis Kanavos Dec 15 '20 at 19:55
  • Both `select cast('2020-11-25T14:22:41.3539327Z' as datetime2)` and `select cast('2020-11-25T14:22:41.3539327Z' as datetimeoffset)` work just fine. `Z` indicates that the time is in UTC. If you care about timezones, convert to `datetimeoffset`. Casting to `datetime2` won't convert the time to the local timezone – Panagiotis Kanavos Dec 15 '20 at 19:57

2 Answers2

1

Provided that all your data is in the format yyyy-MM-ddThh:mm:ss.nnnnnnn then you can just change the data type of the column:

ALTER TABLE dbo.YourTABLE ALTER COLUMN YourColumn datetime2(7);

If you need the timezone in there, then use datetimeoffset(7) instead.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • I had to remove a default constraint, but this works. I assume every solution here would've worked too, but the default constraint was the real problem. Thanks! – Patrick Dec 15 '20 at 20:16
0
-- to fix it you can use convert, below uses GetDate for examaple
SELECT convert(varchar, getdate(), 120)

depending on what precision you need you can use the link below to find it and change the 120 to whatever number you need for that precision.

https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

To fix your table you should follow these steps:

  1. Add a new column to your table for DateTime
  2. Run an update on your table using the Convert above to update the new column with the converted value from your varchar field.
  3. Then drop the column with the varchar data.

Code to do the steps I outlined above

ALTER TABLE dbo.TableName ADD NewDateTimeCOL DATETIME

-- NOTE if your table is LARGE you will not want to do a direct update like this but do looping for performace purposes
UPDATE dbo.TableName
SET NewDateTimeCOL = convert(varchar, OldDateTimeCOL, 120)


ALTER TABLE dbo.TableName DROP COLUMN OldDateTimeCOL
Brad
  • 3,454
  • 3
  • 27
  • 50
  • This will lose accuracy, `datetime` is only accurate to 1/300th of a second. – Thom A Dec 15 '20 at 19:48
  • It doesn't appear that the format I'm using (notice the Z afterwards to represent universal time, from Java's ZonedDateTime class). Am I missing something? To be clear I don't need precision more precise than 1 second. – Patrick Dec 15 '20 at 19:50
  • @FastQ the `Z` is no problem. In fact, that particular format is the easiest to cast as it's unambiguous - there's no way to confuse it due to localization settings. That's not a Java format, that's the ISO8601 datetime format – Panagiotis Kanavos Dec 15 '20 at 20:01
  • I'm having trouble even when testing without the `Z`. "Conversion failed when converting date and/or time from character string." Also unable to convert with SSMS. – Patrick Dec 15 '20 at 20:10
  • @FastQ it sounds like you have some dates stored in some other format. Because the one posted works fine. You may need to use `try_convert` to identify the failing dates. – Dale K Dec 15 '20 at 20:13
  • The way I have it above (if you do it in steps) and you get errors, you wont loose any data or anything. If you try to just change the format on the column directly it could cause issues (or take a really long time if your table is large). – Brad Dec 15 '20 at 20:18
  • @DaleK it seems the default constraint was the problem. I was only testing on a single date entry, the one you saw. https://stackoverflow.com/questions/43549413/alter-table-drop-column-failed-because-one-or-more-objects-access-this-column helped me after I got around to trying Larnu's solution and encountered that issue. – Patrick Dec 15 '20 at 20:18