0

I have a table with a varchar column (dateandtime) with date and time in the format dd/mm/yyyy hh:mm:ss and I need to convert it to a datetime column.

I have a temp column dateandtimetemp in the table .

Thanks

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
user2119912
  • 101
  • 1
  • 2
  • 5

4 Answers4

4

Try this: 

SELECT CONVERT(Datetime, '15/05/2013 13:55:12', 104)

It should return : 2013-05-15 13:55:12.000

Semih Yagcioglu
  • 3,903
  • 1
  • 25
  • 43
2

Try 

SELECT CONVERT(VARCHAR(30),GETDATE(),113) ;

it return result in following format

15 May 2013 16:26:29:850

Abhishek B Patel
  • 867
  • 10
  • 13
1

So then just go ahead and try it!

UPDATE dbo.YourTable
SET DateAndTimeTemp = CAST(DateAndTime AS DATETIME)

and see if it works. If your input data is really always properly defined - you should have no issues here.

This of course depends on what langauge/dateformat setting you have activated in your database - so that might be the first problem you encounter.

If you do have issues, then you can always "clean up" your input data and try again ...

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
0

Try this

SELECT CONVERT(DATETIME, '15/05/2013 11:12:13', 103)
Lithu T.V
  • 19,707
  • 11
  • 54
  • 98
heikkim
  • 2,875
  • 2
  • 23
  • 34