Click here to Skip to main content
12,510,403 members (47,996 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
Dear All,

i have two date selection and in database i stored in varchar datatype with dd/MM/yyyy format.


so, at present when i select date1 as 12/08/2012 and date2 as 12/08/2012 in the MM/dd/yyyy formate so no data display but there are 10 records on that day.

i use :

SELECT      convert(datetime,User_log.sign_in,103) as Sign_in, convert(datetime,User_log.sign_out,103) as Sign_out, User_log.user_name, User_log.document_name, empl.ID FROM   empl INNER JOIN
User_log ON empl.name = User_log.user_name where  convert(datetime,sign_in,103)>='{?date1}' and convert(datetime,sign_in,103)<='{?date2}' 

here date1 is fromdate and date2 is to_date in MM/dd/yyyy.

if i select 12/08/2012 and 12/09/2012, so it display the record with in 8th date.

so, what the problem is ?


Please HElp.......
Mitesh
Posted 9-Dec-12 23:10pm
Updated 10-Dec-12 7:46am
Jibesh17.4K
v2
Comments
Kiran Susarla 10-Dec-12 5:32am
   
In the convert function 103 style is the British/French date format which is dd/mm/yyyy. I am assuming the problem is with the date format you are applying. please check it once again.
CHill60 10-Dec-12 5:57am
   
If you must store dates as varchar on the database then use an unambiguous format - e.g. yyyy-MMM-dd so that 2012-AUG-12 can never be confused with 2012-DEC-08.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi,

This problem is due to date convertion in query " convert(datetime,sign_in,103) ",
while you convert your date, it consider date with hour Eg: 12/08/2012 00:00:00. so the query select date between 12/08/2012 00:00:00 and 12/09/2012 00:00:00.


1.) it will display full data for date 12/08/2012 00:00:00 to 12/09/2012 00:00:00(24 hours only)
2.) 0th hour and 0th min for 12/09/2012 00:00:00

Try this query

SELECT convert(datetime,User_log.sign_in,103) as Sign_in, convert(datetime,User_log.sign_out,103) as Sign_out, User_log.user_name, User_log.document_name, empl.ID FROM empl INNER JOIN
User_log ON empl.name = User_log.user_name where convert(varchar(10),convert(datetime,sign_in,103),103)>='{?date1}' and convert(varchar(10),convert(datetime,sign_in,103),103)<='{?date2}'
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160929.1 | Last Updated 10 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100