procedure and datetime
-
I have proc as below: It don't understand the datetime type that I input I don't know why please help me. Thanks. ALTER PROCEDURE SP_PHIEUNHAP_Search ( @FromNgayNhap datetime, @ToNgayNhap datetime, @CheckNgayNhap bit, @MaNCC int, @FromNgayLap datetime, @ToNgayLap datetime, @CheckNgayLap bit, @NguoiLap int ) AS DECLARE @sql nvarchar(500) DECLARE @flag bit declare @tungay datetime declare @denngay datetime SELECT @sql = ' select MaPN, NgayNhap, TenNCC, NgayLap, HoTen, TongTien ' SELECT @sql = @sql + ' from PHIEUNHAP, NHACUNGCAP, NGUOIDUNG ' if (@CheckNgayNhap = 1 ) begin SELECT @sql = @sql + ' where NgayNhap >= ' + CONVERT(NVARCHAR, @FromNgayNhap,111) + ' and NgayNhap <= ' + CONVERT(NVARCHAR, @ToNgayNhap,111) set @flag = 1 end if(@CheckNgayLap = 1) begin if(@flag = 1) SELECT @sql = @sql + ' and NgayLap >= ' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) else SELECT @sql = @sql + ' where NgayLap >= ' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) set @flag = 1 end if(@NguoiLap != -1) begin if(@flag = 1) SELECT @sql = @sql + ' and NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap) else SELECT @sql = @sql + ' where NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap) set @flag = 1 end if(@MaNCC != -1) begin if(@flag = 1) SELECT @sql = @sql + ' and PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC) else SELECT @sql = @sql + ' where PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC) set @flag = 1 end SELECT @sql = @sql + ' and NguoiLap = MaND and PHIEUNHAP.MaNCC = NHACUNGCAP.MaNCC ' EXEC sp_executesql @sql -- modified at 4:31 Saturday 14th January, 2006
-
I have proc as below: It don't understand the datetime type that I input I don't know why please help me. Thanks. ALTER PROCEDURE SP_PHIEUNHAP_Search ( @FromNgayNhap datetime, @ToNgayNhap datetime, @CheckNgayNhap bit, @MaNCC int, @FromNgayLap datetime, @ToNgayLap datetime, @CheckNgayLap bit, @NguoiLap int ) AS DECLARE @sql nvarchar(500) DECLARE @flag bit declare @tungay datetime declare @denngay datetime SELECT @sql = ' select MaPN, NgayNhap, TenNCC, NgayLap, HoTen, TongTien ' SELECT @sql = @sql + ' from PHIEUNHAP, NHACUNGCAP, NGUOIDUNG ' if (@CheckNgayNhap = 1 ) begin SELECT @sql = @sql + ' where NgayNhap >= ' + CONVERT(NVARCHAR, @FromNgayNhap,111) + ' and NgayNhap <= ' + CONVERT(NVARCHAR, @ToNgayNhap,111) set @flag = 1 end if(@CheckNgayLap = 1) begin if(@flag = 1) SELECT @sql = @sql + ' and NgayLap >= ' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) else SELECT @sql = @sql + ' where NgayLap >= ' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) set @flag = 1 end if(@NguoiLap != -1) begin if(@flag = 1) SELECT @sql = @sql + ' and NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap) else SELECT @sql = @sql + ' where NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap) set @flag = 1 end if(@MaNCC != -1) begin if(@flag = 1) SELECT @sql = @sql + ' and PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC) else SELECT @sql = @sql + ' where PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC) set @flag = 1 end SELECT @sql = @sql + ' and NguoiLap = MaND and PHIEUNHAP.MaNCC = NHACUNGCAP.MaNCC ' EXEC sp_executesql @sql -- modified at 4:31 Saturday 14th January, 2006
Don't use: ... NgayNhap >= ' + CONVERT(NVARCHAR, @FromNgayNhap,111) Just use: ... NgayNhap >= @FromNgayNhap
-
I have proc as below: It don't understand the datetime type that I input I don't know why please help me. Thanks. ALTER PROCEDURE SP_PHIEUNHAP_Search ( @FromNgayNhap datetime, @ToNgayNhap datetime, @CheckNgayNhap bit, @MaNCC int, @FromNgayLap datetime, @ToNgayLap datetime, @CheckNgayLap bit, @NguoiLap int ) AS DECLARE @sql nvarchar(500) DECLARE @flag bit declare @tungay datetime declare @denngay datetime SELECT @sql = ' select MaPN, NgayNhap, TenNCC, NgayLap, HoTen, TongTien ' SELECT @sql = @sql + ' from PHIEUNHAP, NHACUNGCAP, NGUOIDUNG ' if (@CheckNgayNhap = 1 ) begin SELECT @sql = @sql + ' where NgayNhap >= ' + CONVERT(NVARCHAR, @FromNgayNhap,111) + ' and NgayNhap <= ' + CONVERT(NVARCHAR, @ToNgayNhap,111) set @flag = 1 end if(@CheckNgayLap = 1) begin if(@flag = 1) SELECT @sql = @sql + ' and NgayLap >= ' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) else SELECT @sql = @sql + ' where NgayLap >= ' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) set @flag = 1 end if(@NguoiLap != -1) begin if(@flag = 1) SELECT @sql = @sql + ' and NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap) else SELECT @sql = @sql + ' where NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap) set @flag = 1 end if(@MaNCC != -1) begin if(@flag = 1) SELECT @sql = @sql + ' and PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC) else SELECT @sql = @sql + ' where PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC) set @flag = 1 end SELECT @sql = @sql + ' and NguoiLap = MaND and PHIEUNHAP.MaNCC = NHACUNGCAP.MaNCC ' EXEC sp_executesql @sql -- modified at 4:31 Saturday 14th January, 2006
You have to concatenate " ' " to dates.... alter PROCEDURE SP_PHIEUNHAP_Search ( @FromNgayNhap datetime, @ToNgayNhap datetime, @CheckNgayNhap bit, @MaNCC int, @FromNgayLap datetime, @ToNgayLap datetime, @CheckNgayLap bit, @NguoiLap int ) AS DECLARE @sql nvarchar(500) DECLARE @flag bit declare @tungay datetime declare @denngay datetime SELECT @sql = ' select MaPN, NgayNhap, TenNCC, NgayLap, HoTen, TongTien ' SELECT @sql = @sql + ' from PHIEUNHAP, NHACUNGCAP, NGUOIDUNG ' if (@CheckNgayNhap = 1 ) begin SELECT @sql = @sql + ' where NgayNhap >= ''' + CONVERT(NVARCHAR, @FromNgayNhap,111) + ''' and NgayNhap <= ''' + CONVERT(NVARCHAR, @ToNgayNhap,111) + '''' set @flag = 1 end if(@CheckNgayLap = 1) begin if(@flag = 1) SELECT @sql = @sql + ' and NgayLap >= ''' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) + '''' else SELECT @sql = @sql + ' where NgayLap >= ''' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) + '''' set @flag = 1 end if(@NguoiLap != -1) begin if(@flag = 1) SELECT @sql = @sql + ' and NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap) else SELECT @sql = @sql + ' where NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap) set @flag = 1 end if(@MaNCC != -1) begin if(@flag = 1) SELECT @sql = @sql + ' and PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC) else SELECT @sql = @sql + ' where PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC) set @flag = 1 end SELECT @sql = @sql + ' and NguoiLap = MaND and PHIEUNHAP.MaNCC = NHACUNGCAP.MaNCC ' EXEC sp_executesql @sql :suss: