Click here to Skip to main content
Rate this: bad
good
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 10-Dec-12 0:10am
Edited 10-Dec-12 8:46am
Jibesh16.7K
v2
Comments
Kiran Susarla at 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 at 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
0 DamithSL 400
1 Maciej Los 222
2 OriginalGriff 213
3 BillWoodruff 130
4 Zoltán Zörgő 85
0 OriginalGriff 7,969
1 DamithSL 6,139
2 Sergey Alexandrovich Kryukov 5,449
3 Maciej Los 5,309
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web04 | 2.8.141223.1 | Last Updated 10 Dec 2012
Copyright © CodeProject, 1999-2014
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