Format coulmn with dates

Hello All,

I've been struggling with this for a few weeks now. I have this table with the following information:

08/10/2006 10:31:13 AM
08/14/2006 1:32:10 PM
38895.396528
38916.584873
38917.5639
38922.398727
38919.482083
9/20/2006 12:01:19 PM
09/08/2006 1:32:02 PM
38894.73316
38918.543229
38926.564954
08/10/2006 12:31:25 PM
08/10/2006 12:31:25 PM
08/09/2006 1:03:25 PM

What I'm trying to do is get these dates w/timestamps (38926.564954) into this format
08/09/2006 1:03:25 PM . Please help!!!!! Thanks.

Is this what you're trying to do?

select cast(38926.564954 as datetime)

2006-07-30 13:33:32.023

(1 row(s) affected)

It does seem like a valid date, but if 38926 is indeed the numeric form for july 30th 2006, only you can tell.

/Kenneth

|||

Thanks Kenneth. Yes, this is what I'm trying to do, but when I try to format the entire column I receive the following error:

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting datetime from character string

I don't know what to try next. I've tried Cast/Convert but no luck. Please help if you can. Thank you.

|||

Could you change your column to float and rerun the query like this?

SELECT CAST(yourDateMix as datetime) as alldateformat

FROM datemixTable

|||

You might have invalid values in the column which cannot be converted to datetime successfully. I am not sure why you have datetime values in character format. This will lead to lot of problems - one of which you have encountered. You can perform conditional CAST using CASE to avoid the conversion error like:

select cast(case isdate(t.dtcol) when 1 then t.dtcol end as datetime)

from your_table

But the best strategy is to convert the column to datetime and the format the values when you read the data based on the client requirements. Storing values of a particular type in their native format has lot of advantages - managability, efficiency, domain validations, data handling using built-ins, performance and others.

|||This is what I assumed. You're right regarding managabilty. Thanks for your help.