Solved

Need help with a query

Posted on 2016-08-05
7
72 Views
Last Modified: 2016-08-05
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.
0
Comment
Question by:RadhaKrishnaKiJaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41744637
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
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 41744649
thank you for your reply. It is defined as nchar(8) and in 'mmddyyyy' format
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41744660
Is your database SQL Server or MySQL? You categorized your question with both, but they are different products.
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 41744677
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41744729
>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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41744748
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
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 41744756
Thank you.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

710 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