Solved

SQL QUERY DATE Convert from character string

Posted on 2013-01-08
6
610 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

813 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now