Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help with a query

Posted on 2016-08-05
7
Medium Priority
?
80 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

722 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