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.