DateTime Comparison and Conversion
I have been beating my head against the wall on this one, so I would really like to get some help. I have a system that I am trying to pull data into, but the source data is junk and in a terrible format.
I have a query that converts what they call datetime into a datetime format and would like to compare that against a placeholder that is also datetime. I am running into an error where I cannot compare the two datetimes without running into a 'Syntax error converting datetime from character string." and for the life of me I cannot figure it out.
My query is below:
Declare
@StartTime datetime,
@TableName nvarchar(128),
@sql nvarchar(4000)
set @StartTime = '2011-07-07 09:00:00'
set @TableName = 'dbo.a2002opt'
set @sql = '
select
dateadd(second, 15, cast(substring(datetime, 5, 19) as datetime)) as ''Timestamp'',
casingpress39 as ''CasingPressure'',
staticpress39 as ''StaticPressure'',
flowrate39 as ''Flowrate'',
from ' + @TableName + '
where dateadd(second, 15, cast(substring(datetime, 5, 19) as datetime)) > ' + @StartTime + '
Order by ''Timestamp''
'
exec (@sql)
Any assistance would be greatly appreciated. Thank you
I have a query that converts what they call datetime into a datetime format and would like to compare that against a placeholder that is also datetime. I am running into an error where I cannot compare the two datetimes without running into a 'Syntax error converting datetime from character string." and for the life of me I cannot figure it out.
My query is below:
Declare
@StartTime datetime,
@TableName nvarchar(128),
@sql nvarchar(4000)
set @StartTime = '2011-07-07 09:00:00'
set @TableName = 'dbo.a2002opt'
set @sql = '
select
dateadd(second, 15, cast(substring(datetime, 5, 19) as datetime)) as ''Timestamp'',
casingpress39 as ''CasingPressure'',
staticpress39 as ''StaticPressure'',
flowrate39 as ''Flowrate'',
from ' + @TableName + '
where dateadd(second, 15, cast(substring(datetime, 5, 19) as datetime)) > ' + @StartTime + '
Order by ''Timestamp''
'
exec (@sql)
Any assistance would be greatly appreciated. Thank you
ASKER
Hi there,
No rows returned where isdate(substring(datetime,
A couple notes I failed to mention yesterday (sorry), I am trying to build this query to be able to take input for @starttime and @tablename, which is why it's structured the way it is. My rows are all valid, the database is just poorly organized.
The column name "datetime" is actually char(30) and reads like: 'Mon 07/17/2011 16:10:00' which is why I cast the substring into a datetime point type. My other selected columns are floats.
To add further clarification, when I have a query that reads:
where dateadd(second, 15, cast(substring(datetime, 5, 19) as datetime)) > ''2011-07-07 09:00:00'' -- my output is fine
where dateadd(second, 15, cast(substring(datetime, 5, 19) as datetime)) > ' + @StartTime + ' -- I get my string conversion error.
As I mentioned before, any assistance is appreciated and thank you for taking a look at this.
Queried-Table.PNG
Raw-Table.PNG
No rows returned where isdate(substring(datetime,
A couple notes I failed to mention yesterday (sorry), I am trying to build this query to be able to take input for @starttime and @tablename, which is why it's structured the way it is. My rows are all valid, the database is just poorly organized.
The column name "datetime" is actually char(30) and reads like: 'Mon 07/17/2011 16:10:00' which is why I cast the substring into a datetime point type. My other selected columns are floats.
To add further clarification, when I have a query that reads:
where dateadd(second, 15, cast(substring(datetime, 5, 19) as datetime)) > ''2011-07-07 09:00:00'' -- my output is fine
where dateadd(second, 15, cast(substring(datetime, 5, 19) as datetime)) > ' + @StartTime + ' -- I get my string conversion error.
As I mentioned before, any assistance is appreciated and thank you for taking a look at this.
Queried-Table.PNG
Raw-Table.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect. Absolutely perfect. Worked like a charm. I don't understand though why I had to convert it to a varchar before it would compare to a datetime.
ASKER
Beautiful.
To answer your last question, the reason is you are using dynamic SQL; therefore, SQL gets confused over the (+) operation on a string and a datetime. It has to implicitly convert one to the other. If it tries the string, then you are guaranteed to fail as a SQL query tends not to evaluate to a convertible date. To alleviate this, we explicit convert the date to a string then it understands (+) means "concatenate."
If you are not sure, replace this:
where dateadd(second, 15, cast(substring(datetime, 5, 19) as datetime)) > ' + @StartTime + '
with:
where isdate(substring(datetime,
And change the select to show the value, i.e.:
select substring(datetime, 5, 19) as ''Timestamp''
Please post back a sample of those rows.