Solved

DateTime Comparison and Conversion

Posted on 2011-09-13
6
317 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
  • 3
  • 3
6 Comments
 
LVL 59

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 59

Accepted Solution

by:
Kevin Cross earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 59

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need some help wiht :CAST AS Double 11 45
Help with SQL joins 9 47
Email Notifications for SQL 2005 9 27
Query group by data in SQL Server - cursor? 3 28
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

815 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now