date problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

date problem

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-02 : 01:23:19

hi this is my query:

select from projectmaster
where project_start_date between convert(datetime,@fromdate,103) and convert(datetime,@todate,103)
so i have the following date in my table:

select project_start_date from projectMaster

project_start_date
----------------------
2007-02-27 16:53:27.000
2007-02-28 13:05:52.003
2007-03-01 09:22:09.000
2007-03-01 14:09:23.000
2007-03-01 15:54:15.980
2007-03-01 16:08:15.297
2007-03-01 14:03:39.000

here my problem is if run the first query i am getting records of these two dates only:
2007-02-27 16:53:27.000
2007-02-28 13:05:52.003

for example
select from projectmaster
where project_start_date between convert(datetime,'27-02-2007',103) and convert(datetime,'01-03-2007',103)

i am getting only two rows but i need all the rows has to be dispplayed. please help me to achieve my need.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-02 : 01:29:37

[code]Where dateadd(d, 0, datediff(d, 0, project_start_date)) between '20070227' and '20070301'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-02 : 01:45:48

hi harsh_athalye, i am passing date as dd/mm/yyyy(27-02-2007) from my frontend but u used another format(20070227).can u please

Where dateadd(d, 0, datediff(d, 0, project_start_date)) between '20070227' and '20070301'

how to use my dateformat(dd/mm/yyyy) to pass here?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-02 : 02:19:30

[code]Where dateadd(d, 0, datediff(d, 0, project_start_date)) between convert(datetime, '27-02-2007', 105) and convert(datetime, '01-03-2007', 105)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 03:12:49

Use DATETIME parameters in the stored procedure for handling dates and times correctly.
Do not use VARCHAR for this.

Peter Larsson
Helsingborg, Sweden

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-02 : 03:14:01

hi harsh,i used ur query it dint dive reords,i am getting empty only but i have values in that.
select project_start_date from projectMaster

project_start_date
----------------------
2007-02-27 16:53:27.000
2007-02-28 13:05:52.003
2007-03-01 09:22:09.000
2007-03-01 14:09:23.000
2007-03-01 15:54:15.980
2007-03-01 16:08:15.297
2007-03-01 14:03:39.000

i used this query:
select * from projectmaster Where dateadd(d, 0, datediff(d, 0, project_start_date)) between convert(datetime, '27-02-2007', 105) and convert(datetime, '01-03-2007', 105)

but i am not getting answer empty row only shown,please help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 03:18:22

select *
from projectmaster
Where project_start_date >= '20070227'
and project_start_date < '20070302'

Peter Larsson
Helsingborg, Sweden

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-02 : 03:23:01

hi peso, i used this query:

select *
from projectmaster
Where project_start_date >= convert(datetime, '27-02-2007', 105)
and project_start_date < convert(datetime, '01-03-2007', 105)

why i am converting here is i am passing date as dd/mm/yyy formatbut i am getting empty recods only,so please help me

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-02 : 03:31:17

hi peso,

i used this:
select project_start_date
from projectMaster
Where project_start_date >= convert(datetime, '22-02-2007', 105)
and project_start_date < convert(datetime, '02-03-2007', 105)

i am getting only two values:
project_start_date
----------------------
2007-02-26 13:55:59.093
2007-02-28 13:56:49.923

but i have the following values in my table:

project_start_date
----------------------
2007-03-02 13:27:49.973
2007-02-26 13:55:59.093
2007-02-28 13:56:49.923
2007-03-02 13:28:46.513
2007-03-02 13:55:05.907
2007-02-27 14:01:44.093
2007-03-02 13:47:11.063

so what is the problem why i couldn't get help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 03:43:49

quote:
Originally posted by sqllover

select project_start_date
from projectMaster
Where project_start_date >= convert(datetime, '22-02-2007', 105)
and project_start_date < convert(datetime, '02-mar-2007')

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 03:44:11

select project_start_date
from projectMaster
Where project_start_date >= convert(datetime, '22-02-2007', 105)
and project_start_date < convert(datetime, '03-mar-2007')

Peter Larsson
Helsingborg, Sweden

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-02 : 04:02:11

hi peso, i am passing date in dd/mm/yyyy

u used as 03-mar-2007 so how can i convert 03/03/2007 as 03-mar-2007 and u used 03-mar-2007 so that i can getall the values but i have
02/03/2007 so why u have given 03 as date.can't we give 02 as date na.

becos i have records like this:
project_start_date
----------------------
2007-03-02 13:27:49.973
2007-02-26 13:55:59.093
2007-02-28 13:56:49.923
2007-03-02 13:28:46.513
2007-03-02 13:55:05.907
2007-02-27 02:01:44.000
2007-03-02 13:47:11.063

noe u can clear my queston

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 04:31:46

The TIME part when only given dd/mm/yyyy is 00:00:00.000.

You have several records with the same date for March 2nd, but the TIME is between 1 and 2 pm.
So when you compare with only 02/03/2007, you miss out the records in the afternoon.

Therefore you should compare with the following day and have all records LESS than following day.

Peter Larsson
Helsingborg, Sweden

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-02 : 04:33:19

ok peso thanks for your big coneversation nice reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources