[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL QUERY DATE Convert from character string

Posted on 2013-01-08
6
Medium Priority
?
660 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 23

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
Industry Leaders: 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!

 
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 2000 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

Industry Leaders: 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!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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