Solved

SQL QUERY DATE Convert from character string

Posted on 2013-01-08
6
622 Views
Last Modified: 2013-01-08
Given the following query, I need add an additional where clause that filters out all rows that contain "date.today" in feild A.UserDef4.  A.UserDef4 is a varchar(50) holding a date in format mm/dd/yy.

SELECT        *
FROM            dbo.A INNER JOIN
                         dbo.B ON dbo.A.ID = dbo.B.ID INNER JOIN
                         dbo.C ON dbo.B.ID = dbo.C.ID
WHERE        (dbo.C.ID = @ID)
ORDER BY dbo.A.Name

I have added the following to the where statement:

and (
datepart(year,Convert(datetime,A.UserDef4)) <> datepart(year,GetDate()) and
datepart(month,Convert(datetime,A.UserDef4)) <> datepart(month,GetDate()) and
datepart(day,Convert(datetime,A.UserDef4)) <> datepart(day,GetDate())
)

I recieve an error that states "Conversion failed when converting datetime from character string."

Please help....
0
Comment
Question by:dtechfish
6 Comments
 
LVL 20

Expert Comment

by:informaniac
ID: 38754733
Does A.UserDef4 store date in dd/mm/yyyy format? That can cause the issue
0
 

Author Comment

by:dtechfish
ID: 38754752
Yes it does, I will try and modify the imput date format.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38754812
Telling the convert function that your year is in dd/mm/yyyy should help, that's what the convert option 103 does:

and(
datepart(year,Convert(datetime,A.UserDef4,103)) <> datepart(year,GetDate()) and
datepart(month,Convert(datetime,A.UserDef4,103)) <> datepart(month,GetDate()) and
datepart(day,Convert(datetime,A.UserDef4, 103)) <> datepart(day,GetDate())
)

Open in new window


See CAST and CONVERT in BOL: http://msdn.microsoft.com/en-us/library/ms187928.aspx

However, be aware that functions in a where clause can cause nasty performance issues on a large table.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 13

Expert Comment

by:LIONKING
ID: 38754881
If your format is "always" mm/dd/yy, then you can use the convert function with the style option, as stated above. You can also use the getdate in one single statement (without time).

Something like:

AND CONVERT(DATETIME, A.UserDef4, 1) <> DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))

Open in new window


Link to CONVERT styles here
0
 
LVL 12

Accepted Solution

by:
Jared_S earned 500 total points
ID: 38754882
1) that seems like a lot of code to make sure that A.UserDef4 isn't today. You could do the same thing with:

A.UserDef4  <> convert(varchar,getdate(),1)

2) Since you're date field doesn't have a date value type, try searching it for any alpha characters with something like this:

select myDate from myTable where patindex('%[A-Z]%', myDate) <> 0
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38754893
Just to add to my previous post... You should indeed consider that using functions in your where clause will have an impact on performance (as already said), so keep that in mind.

Hope it helps.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 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