BETWEEN operator for select query in sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

BETWEEN operator for select query in sql server

Author

Topic

manisha.vk
Starting Member

17 Posts

Posted - 2011-10-04 : 01:41:15

I am working on sql server 2008.
I am running a simple query as follows

SELECT * from EmpAttendanceRegister WHERE LogDate BETWEEN '10/01/2011' and '10/30/2011'

It is not showing the records.
There are records which has LogDate between given dates but still it is not showing the result.

Datatype of LogDate column is Date.

Can anybody help me out for this issue.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 02:04:24

[code]SELECT * from EmpAttendanceRegister WHERE LogDate >='20111001' and LogDate < '20111031'[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manisha.vk
Starting Member

17 Posts

Posted - 2011-10-04 : 02:22:54

I tried with the same query but it is also not working.
if i run separately for >= and < then also it was not running.
Sql server is storing date in yyyy-dd-mm format.
So i changed the format of date in your query to '20110110' and '20113110'
then it is working for

SELECT * from EmpAttendanceRegister WHERE LogDate >='20110110'
but for
SELECT * from EmpAttendanceRegister WHERE LogDate < '20113110'
it is showing the error as
Conversion failed when converting date and/or time from character string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 02:30:03

Sql server is storing date in yyyy-mm-dd format not yyyy-dd-mm thats why error. i didnt understand why it didnt work as >= and < is working for me. are you sure datatype is date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manisha.vk
Starting Member

17 Posts

Posted - 2011-10-04 : 03:04:53

for storing date values in table it is storing in yyyy-dd-mm format but for checking it is taking yyyy-mm-dd format.

now
SELECT * from EmpAttendanceRegister WHERE LogDate >'20111001'
this query is not working means it is not showing any result.

but
SELECT * from EmpAttendanceRegister WHERE LogDate < '20111030'
this query is working
i tried something else like this
SELECT * from EmpAttendanceRegister WHERE LogDate < '20110330'
actually it should all records before 30th march but it is showing only 1st to 4th october entries.

I am not getting what is happening.
Can i change the sql server date format to dd-mm-yyyy if yes how to do.

Thanks for your help.

manisha.vk
Starting Member

17 Posts

Posted - 2011-10-04 : 03:27:05

sorry the problem with my understanding only,
sql server is storing date as yyyy-mm-dd only.

now the problem is i am entering data through vb.net so the input for LogDate is going in a dd/mm/yyyy format.
like this
INSERT INTO [QICLDB].[dbo].[EmpAttendanceRegister] ([EmpId],[LogDate],[LogStatus],[LogINTime]) VALUES (310,'04/10/2011','IN','11:17 AM')

database storing it as 04 as month 10 as date and 2011 as year.

Is it required that the input should be in database date storing format only?
if it is like that then what is feasible to change the database format or data input format.

Sorry I am troubling you a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 04:13:28

always try to pass dates in yyyymmdd format as its unambiguos
if dates come from your application in dd/mm/yyyy format apply format functions there to make it in yyyymmdd format before passing to sql query. then it will avoid all confusions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manisha.vk
Starting Member

17 Posts

Posted - 2011-10-04 : 05:07:23

Thank you Vishakh
I have change it in vb application only
by applying Format function.
Thanks for your support.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 05:11:53

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources