var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: varchar to datetime
im trying to convert from varchar to datetime. Yes i know its horrible to store date in varchar, but its built this way and im stuck with that.
This: (column as varchar(24))
Gives me: "2010-09-15 13:45:49.809"
what i dont understand is why this:
CONVERT(datetime, DateTimeOnAction, 121)
gives me: "Conversion failed when converting date and/or time from character string."
thx in advance
So you have bad data in your table. Try using the IsDate function to find it:
declare @DateTimeOnAction varchar(50)
set @DateTimeOnAction = '2010-09-15 13:45:49.809'
select CONVERT(datetime, @DateTimeOnAction, 121)
(1 row(s) affected)
select * from TData where IsDate(DateTimeOnAction) <> 1
every single row i returned when i use your suggestion above
and when i use this:
where DateTimeOnAction = '2010-09-15 13:45:49.810'
it also return 0 :s
That seems wierd. When I run this:
SELECT ISDATE('2010-09-15 13:45:49.810')
I get a 1 to return.
Webmaster at SQL Optimizations School
Yes i got the same result, it's à valid dateformat. But when i run isdate against the table it's not valid...
it works :O
it's working guys..
select convert(datetime, <colname>) from tablename
where is teh problem?:O
i create a table
CREATE TABLE [dbo].[CounterData_dev](
[CounterDateTime] [varchar](24) NULL
) ON [PRIMARY]
did an insert from my table to the one above.
ex. data in my newly created table
None of this rows can be identified as valid datetime value with ISDATE, what on earth is wrong here?
When i run the above values directly like "select ISDATE('2011-07-05 15:25:34.849')" this returns 1, so why can't the rows in the table be identified as valid datetime value?
solved it, the column is one character to big.
when i remove one character it works and it's identified as a valid datetime: cast(cast(CounterDateTime as varchar(23)) as DATE)
can someone please explain why ltrim(rtrim(CounterDateTime)) dosen't work?
Check the following code:
Only the last one returns 0 (false). My guess is that these dates has been added by an application with a trailing ASCII 0 character (string termination). ASCII 0 characters are not eliminated by ltrim/rtrim.
declare @a table (
insert into @a values
('2011-07-05 15:25:34.849 '),
(' 2011-07-05 15:25:34.849'),
select ISDATE(val) from @a
select LEN(ltrim(rtrim(val))) from @a
oh nice, thats must be the case.
Thx for your help.