SQL QUERY DATE Convert from character string

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....
dtechfishAsked:
Who is Participating?
 
Jared_SConnect With a Mentor Commented:
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
 
informaniacCommented:
Does A.UserDef4 store date in dd/mm/yyyy format? That can cause the issue
0
 
dtechfishAuthor Commented:
Yes it does, I will try and modify the imput date format.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Steve WalesSenior Database AdministratorCommented:
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
 
LIONKINGCommented:
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
 
LIONKINGCommented:
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
All Courses

From novice to tech pro — start learning today.