?
Solved

Need help with a query

Posted on 2016-08-05
7
Medium Priority
?
85 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
7 Comments
 
LVL 27

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 27

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 27

Accepted Solution

by:
Shaun Kline earned 2000 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 66

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 30

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

579 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