Convert String to Date and Use in Criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convert String to Date and Use in Criteria

Author

Topic

jmiskey
Starting Member

15 Posts

Posted - 2007-12-17 : 14:06:28

I have a field (named Field5) that is a string that looks something like:

A:01/01/2007:12/31/2007

What I need to do is pull the last ten characters of that field, convert to a date, and compare to the current date. Unfortunately, I cannot seem to get it to work. I tried using the CONVERT function, but maybe it doesn't work like I expect it to. Here is my code:

SELECT convert(varchar(10),Right(Field5,10),101) as End_Date,*
FROM Company_Master
WHERE convert(varchar(10),Right(Field5,10),101)>getdate()

This gives me a "Syntax error converting datetime to character string." error.

What am I doing wrong? How can I accomplish this?

Thanks.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-17 : 14:19:33

You are comparing a varchat with a datetime? You might want to convert to datetime rather than varchar before you compare. Just FYI the performance might not be on par when using functions on columns in WHERE conditions.


SELECT convert(varchar(10),Right(Field5,10),101) as End_Date,*
FROM Company_Master
WHERE convert(Datetime,Right(Field5,10)) > getdate()

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jmiskey
Starting Member

15 Posts

Posted - 2007-12-17 : 14:27:51

dinakar,

That does not work either. I still get the same error. There may be some bad records in the field, i.e. there a few with just an "A" with nothing else after it. Do you think that might be part of the problem?

I guess I don't fully understand the arguments of the CONVERT function. How does the function know which format my date is in (i.e. mm/dd/yyyy or dd/mm/yyyy)? I imagine if it doesn't know that, it may have a hard time converting the string to date.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-17 : 14:34:39

quote:
Originally posted by jmiskey

dinakar,

That does not work either. I still get the same error. There may be some bad records in the field, i.e. there a few with just an "A" with nothing else after it. Do you think that might be part of the problem?

Ofcourse that IS the problem.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jmiskey
Starting Member

15 Posts

Posted - 2007-12-17 : 17:03:39

quote:
Ofcourse that IS the problem.

Sorry, I guess I was expecting those individual records to have errors, but wasn't expecting the whole query to fail.

I will run it through Microsoft Access where I have a bit more control/knowledge/functionality to fix these data problems. I am running it through Access in the end anyway, I was just hoping to fix up some of the data before it got there.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 17:15:18

Add a second WHERE criteria, as

AND Field5 LIKE '%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources