Solved

Need help with a query

Posted on 2016-08-05
7
58 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 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

20 Experts available now in Live!

Get 1:1 Help Now