Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DateTime Comparison and Conversion

Posted on 2011-09-13
6
Medium Priority
?
348 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

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…
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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

916 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