CODEWITHSUNDEEP
sql-server-2008datetimevarchar
user2496503
user2496503

Reputation: 927

Convert varchar to datetime SQL Server 2008

I have a column of Date and Time like this:

  20/Mar/2013:02:28:47
  20/Mar/2013:02:28:43
  20/Mar/2013:02:28:47
  20/Mar/2013:02:28:47
  20/Mar/2013:02:28:47
  20/Mar/2013:02:28:47
  20/Mar/2013:02:28:41
  20/Mar/2013:02:28:46

the datatype of this column is set to "varchar". I just need to convert the following format of column to: 

  20/03/2013:02:28:47
  20/03/2013:02:28:43
  20/03/2013:02:28:47
  20/03/2013:02:28:47
  20/03/2013:02:28:47
  20/03/2013:02:28:47
  20/03/2013:02:28:41
  20/03/2013:02:28:46

also can i change its datatype from varchar to SQL date and time datatype! I am trying with my query like:

 SELECT convert(mydatetimecolumn,105) from mytable

but it gives me the result like:

   1900-04-16 00:00:00.000
   1900-04-16 00:00:00.000
   1900-04-16 00:00:00.000
   1900-04-16 00:00:00.000
   1900-04-16 00:00:00.000

How can I overcome this problem? can somebody help me please? Thanks in advance!

Upvotes: 1

Views: 7613

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280570

Here is one possible solution, assuming that you don't have any junk in this column that can't be converted correctly (which is entirely possible, since for some reason you decided to store dates as strings).

Note that this is a sample and not something you can apply directly to your existing table. This is on purpose. It is intentionally created in tempdb and with fictitious/meaningless table names so you can try it out instead of trying anything directly against your production table first. Because, what happens if you run an update against your real table, and it doesn't work like you expect? Do you have a backup?

USE tempdb;
GO

CREATE TABLE dbo.floob(splunge VARCHAR(32));

INSERT dbo.floob(splunge) VALUES('20/Mar/2013:02:28:47'),('4/Mar/2013:02:25:32');

UPDATE dbo.floob SET splunge = CONVERT(CHAR(8), 
  CONVERT(DATETIME, REPLACE(LEFT(splunge,
  CHARINDEX(':', splunge)-1), '/', ' '), 13), 112) 
  + ' ' + RIGHT(splunge, 8);

SELECT splunge FROM floob;
GO

SELECT CONVERT(DATETIME, splunge) FROM floob;
GO

ALTER TABLE dbo.floob ALTER COLUMN splunge DATETIME;
GO

SELECT splunge FROM floob;
GO

For your real table, again assuming there is no junk in the table, you would just do the UPDATE and ALTER parts (of course putting in your real table and column names instead of floob and splunge).

SQLfiddle example

Upvotes: 4

Alex K.
Alex K.

Reputation: 175936

One way;

declare @s varchar(32) = '20/Mar/2013:02:28:46'

select cast(stuff(replace(@s, '/', ' '), 12, 1, ' ') as datetime)

(No column name)
2013-03-20 02:28:46.000

Use

cast(stuff(replace(@s, '/', ' '), len(@s) - 8, 1, ' ') as datetime)

If the day is d or dd.

Upvotes: 0

Related Questions