Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DateTime Comparison and Conversion

Posted on 2011-09-13
6
Medium Priority
?
345 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:MeraGroup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36533150
Hi. Do you have examples of the data that is failing?
If you are not sure, replace this:

where dateadd(second, 15, cast(substring(datetime, 5, 19) as datetime)) > ' + @StartTime + '

with:

where isdate(substring(datetime, 5, 19)) = 0

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.
0
 

Author Comment

by:MeraGroup
ID: 36536604
Hi there,
No rows returned where isdate(substring(datetime, 5, 19)) = 0

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
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36536869
Sounds like the problem is in the @StartTime. What data type is that variable? Try doing this:

where dateadd(second, 15, cast(substring(datetime, 5, 19) as datetime)) > ''' + convert(varchar, @StartTime, 120) + '''

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:MeraGroup
ID: 36536949
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.
0
 

Author Closing Comment

by:MeraGroup
ID: 36536954
Beautiful.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36537009
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."
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question