problem with convert text to time in sql server 2008 ?

Question

hi

i have this Ttime as nvarchar(10): "09:52:48" and i have TmpTime as date

and i try to convert like this: "UPDATE MEN SET TmpTime = CONVERT(DATETIME, Ttime ,108 )"

and i get in TmpTime this: "1900-01-01"

why ?

thank's in advance

Answer 1

You have a column defined as "date" and then you are sending only a time value into it

The date portion defaults to zero which is 01 January 1900 in SQL (in the CONVERT). Then the time is ignored for a date column.

What do you expect to happen?

(The same would happen whether or not you use CONVERT or not because the column is "date")

Answer 2

If you also have a date field, you should to concatenate them before to cast:

CREATE TABLE #Sample ( DateField varchar(10), TimeField varchar(10) );
GO

INSERT INTO  #Sample VALUES ('2009-01-24', '09:52:48');
SELECT CONVERT(DATETIME, DateField + ' ' + TimeField) as Converted FROM #Sample

And you'll get:

Converted
-----------------------
2009-01-24 09:52:48.000