April 14, 2012 at 4:35 am
Hi
i have a filed wich type is datetime : and data in that field are like this:
2012-05-01 00:00:00.000
and i have a parameter like this : 2012/01/01
i want to have a store procedure for searching this parameter in this filed
(i mean parameter without time and "-" instead of "/" )
would you please help me?
April 14, 2012 at 5:32 am
What exactly is your problem as this works for me(Note the extra row of data which does NOT match what you posted):
CREATE TABLE #T(Col1 DATETIME,Col2 INT IDENTITY(1,1))
INSERT INTO #T
SELECT '2012-05-01 00:00:00.000' UNION ALL
SELECT '2012-05-01 01:00:00.000' UNION ALL --extra row
SELECT '2012-05-02 00:00:00.000' UNION ALL
SELECT '2012-05-03 00:00:00.000'
DECLARE @X DATETIME
SET @X = '2012/05/01'
SELECT Col1,Col2 FROM #T WHERE Col1 = @X
/*Result:
Col1 Col2
2012-05-01 00:00:00.000 1 */
April 14, 2012 at 5:37 am
If the datetime column only ever contains date if the time being 00:00:00.000 always then...
create table dbo.SampleData(
ID int primary key,
SampleName varchar(30),
SampleDate datetime
)
go
insert into dbo.SampleData values
(100,'Sample 100','2012-01-01')
,(101,'Sample 101','2012-01-02')
,(102,'Sample 102','2012-01-03')
/* Results show as
100 , Sample 100 , 01/01/2012 00:00:00.000
101 , Sample 101 , 02/01/2012 00:00:00.000
102 , Sample 102 , 03/01/2012 00:00:00.000
*/
go
-- if parameter is datetime
create proc dbo.getSampleData @dt datetime
as
begin
select * from dbo.SampleData
where SampleDate = @dt
end
go
exec dbo.getSampleData '2012-01-03'
go
-- if parameter is varchar with format dd/mm/yyyy then
create proc dbo.getSampleData @dtvc varchar(12)
as
begin
select * from dbo.SampleData
where SampleDate = convert(datetime,@dtvc,103)
end
go
exec dbo.getSampleData '03/01/2012'
go
If the datetime column can contain time as well and you want all the rows for the date regardless of time.
create table dbo.SampleData(
ID int primary key,
SampleName varchar(30),
SampleDate datetime
)
go
insert into dbo.SampleData values
(100,'Sample 100','2012-01-01')
,(101,'Sample 101','2012-01-02')
,(102,'Sample 102','2012-01-03')
,(103,'Sample 103','2012-01-03 11:30')
/* Results show as
100 , Sample 100 , 01/01/2012 00:00:00.000
101 , Sample 101 , 02/01/2012 00:00:00.000
102 , Sample 102 , 03/01/2012 00:00:00.000
103 , Sample 103 , 03/01/2012 11:30:00.000
*/
go
-- if parameter is date
create proc dbo.getSampleData @dt date
as
begin
select * from dbo.SampleData
where convert(date,SampleDate) = @dt
end
go
exec dbo.getSampleData '2012-01-03'
go
-- if parameter is varchar with format dd/mm/yyyy then
create proc dbo.getSampleData @dtvc varchar(12)
as
begin
select * from dbo.SampleData
where convert(date,SampleDate) = convert(date,@dtvc,103)
end
go
exec dbo.getSampleData '03/01/2012'
go
Fitz
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply