Solved

DateTime Comparison and Conversion

Posted on 2011-09-13
6
332 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 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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Impove long SQL Stored Procedure Performance 14 82
Following an example - removing duplicate strings 4 70
Analysis of table use 7 68
SSMS Imprt data from Excel 7 38
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 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