ansaurus

Question

Trying to convert "yy/mm/dd hh:mm:ss" (stored as char) into datetime using computed column

Answer 1

+3 A:

You can add the computed field to the table in SQL Server Management Studio using a query no problem:

ALTER TABLE dbo.YourTable 
  ADD NewDateTimeField AS CONVERT(DATETIME, tx_date + ' ' + time, 11)

but unfortunately, you cannot make it "PERSISTED" since the CONVERT function in non-deterministic. This means it'll be examined each time it's accessed, and you cannot put an index on it :-(

Marc

marc_s 2009-11-28 23:05:51

+1 Right on, as always :)

Andrew Hare 2009-11-28 23:06:38

@Andrew: I'm trying my best, thanks! :-)

marc_s 2009-11-28 23:07:29

I don't need it to be persisted (in fact I don't want it to be because I don't want to grow the database). I was trying to add it as a non persistent column (the 'persist' check box unchecked)It's just for convenience really - fed up of pasting data into excel and having it think the date is the 9th of november 2027.

MrVimes 2009-11-28 23:14:41

and... won't that alter table query just add a new standard column with the computed value actually stored there? It needs to be computed on the fly. (because the data essentially exists elsewhere in the table so I don't want to duplicate data unnecesarily

MrVimes 2009-11-28 23:17:44

As long as you don't have the PERSISTED on it, it's just computed on the fly as needed - it's **NOT** stored anywhere (just the formula is - no values are stored)

marc_s 2009-11-28 23:18:25

Ok I went ahead and did it. Seems to have worked fine. Thanks Marc!

MrVimes 2009-11-28 23:19:52

ansaurus

tags:

views:

answers:

Trying to convert "yy/mm/dd hh:mm:ss" (stored as char) into datetime using computed column

related questions