Need help with a query

Hi Experts,

I have a field called 'UserStartDate' (mmddyyyy) in the table. I want a query to select depending on the date range. My query is-

Select * from UserDetails where UserStartDate >= '01012013' and  UserStartDate <= '12012013'

But I am not getting the records.

Thanks.
RadhaKrishnaKiJayaAsked:
Who is Participating?
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
If you are using SQL Server, try using the CONVERT function on UserStartDate in your WHERE clause:

SELECT * 
FROM UserDetails 
WHERE CONVERT(DATETIME, RIGHT(UserStartDate, 4) + LEFT(UserStartDate, 4), 112) >= '2013-01-01' 
    AND CONVERT(DATETIME, RIGHT(UserStartDate, 4) + LEFT(UserStartDate, 4), 112) <= '2013-12-01'

Open in new window


If that doesn't work, you may need to provide sample data (without any sensitive information) for us to review.
0
 
Shaun KlineLead Software EngineerCommented:
If UserStartDate is defined in your table as date or datetime field, use date format yyyy-mm-dd.
For your query:
Select * from UserDetails where UserStartDate >= '2013-01-01' and  UserStartDate <= '2013-12-01'

Open in new window

0
 
RadhaKrishnaKiJayaAuthor Commented:
thank you for your reply. It is defined as nchar(8) and in 'mmddyyyy' format
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Shaun KlineLead Software EngineerCommented:
Is your database SQL Server or MySQL? You categorized your question with both, but they are different products.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>thank you for your reply. It is defined as nchar(8) and in 'mmddyyyy' format
There's your problem.  It's in character format and not date format, so any expression will evaluate based on the characters from left to right as opposed to by date.  

Shaun's approach is correct in that if you want it to evaluate on date, you have to convert to a date.

A side discussion is that any column that stores dates has no business being any data type other than date, for this exact reason.
2
 
Olaf DoschkeSoftware DeveloperCommented:
You expect a system to have the "common sense" to detect this data means dates and a comparison of them should work. Look close at what you imagine. nchar(8) means to the computer this is text, just a bunch of up to 8 unicode chars.

There is good reason why a standard string format implicitly converted to date is 'YYYYMMDD' and not any other order of the three date parts, can you imagine why?

Bye, Olaf.
0
 
RadhaKrishnaKiJayaAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.