Solved

SQL QUERY DATE Convert from character string

Posted on 2013-01-08
6
591 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
Comment Utility
Does A.UserDef4 store date in dd/mm/yyyy format? That can cause the issue
0
 

Author Comment

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

Expert Comment

by:Steve Wales
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:LIONKING
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

12 Experts available now in Live!

Get 1:1 Help Now