datetime issue for select - 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

datetime issue for select

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-12 : 04:20:23

hi
i have date with time in my column "dob" in my table.

select * from emp where dob='02/30/2007'
it gives empty value
becos i have date with time in my column("dob").

i need to check only date so how to write query to check only date in my "dob" column,can any one give me query to do this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-12 : 04:23:54

quote:
select * from emp where dob='02/30/2007'

February 30 ? ?

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-12 : 04:30:56

sorry, 02/20/2007

if i give like also it gives emptyplease help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-12 : 04:32:46

quote:

select * from emp where dob='02/30/2007'
it gives empty value

You will get "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
as 02/30/2007 is not a valid date

what is the data type of dob ?

if it is datetime


declare @dob datetime

select @dob = '20070201'

select *
from   emp
where  dob >= @dob
and    dob <  dateadd(day, 1, @dob)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-12 : 04:33:51

quote:
Originally posted by sqllover

sorry, 02/20/2007

if i give like also it gives emptyplease help me

See the script that i posted.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-12 : 04:40:43

hi khtan,thanks you very much i got it

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-12 : 04:46:09

hi khtan i used like this,

CREATE PROCEDURE spprojectmaster_report (@dob datetime) as
declare @sql varchar(4000)
begin
select @sql='select * from projectMaster where project_start_date =' + @dob + ''
exec(@sql)
--and project_start_date < dateadd(day, 1, @dob)
end
GO
it gives error as:
Server: Msg 241, Level 16, State 1, Procedure spprojectmaster_report, Line 4
Syntax error converting datetime from character string.
how to solve this error

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-12 : 05:06:22

[code]
declare @sql nvarchar(4000)
select @sql = 'select * from projectMaster where project_start_date = @dob'
exec sp_executesql @sql, N'@dob datetime', @dob
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-12 : 05:08:47

Does the project_start_date also contain date & time ?

use this


CREATE PROCEDURE spprojectmaster_report (@dob datetime) as
begin
  declare @sql nvarchar(4000)
  select @sql = 'select * from projectMaster where project_start_date >= @dob and project_start_date < dateadd(day, 1, @dob)'
  exec sp_executesql @sql, N'@dob datetime', @dob
end

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-12 : 05:52:31

hi khtan, ya i got it using ur help thank you very nuch

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 06:12:01

Why do you feel the need for dynamic SQL in the stored procedure?

CREATE PROCEDURE spprojectmaster_report
(
@dob datetime
)
as

select *
from projectMaster
where project_start_date >= @dob and project_start_date < dateadd(day, 1, @dob)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources