Solved

DateTime Comparison and Conversion

Posted on 2011-09-13
6
308 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard 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.
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…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

21 Experts available now in Live!

Get 1:1 Help Now