December 22, 2003 at 1:42 am
Hi SQL Gurus,
I am facing problem with data type datetime.
I am writing sql queries which will be called from a stored procedure by passing the necessary input variables, for a project which is a web based one. Almost all tables have a column with data type datetime.
I am trying to frame a query dynamically by assigning the same to a varchar local variable and later on executing this variable.
For example my procedure looks like this:
create procedure jobpost
@in_vcjobname varchar(200),
@in_intjobstatus int,
@in_dtstartdatedatetime,
@in_dtenddatedatetime,
@in_vclocationvarchar(50)
as
begin
set quoted_identifier off
set nocount on
declare
@vcQuery varchar(3000),
@intErrorint
set @vcQuery = ''
If exists (selecting whether that same job is existing with the same date)
begin
begin transaction test
set @vcQuery = 'insert into tableA (jobname, jobstatus, startdate, enddate, location, created_date) values ('
set @vcQuery = @vcQuery + "'" + @in_vcjobname + "'" + "," + convert(varchar, @in_intjobstatus) + @in_dtstartdate + @in_dtenddate + "'" + @in_vclocation + "'" + gedtate() )
select @intError = @@error
end
else
begin
Raiserror ('Not valid data', 16, 1)
return -100
end
if (@intError != 0)
begin
Raiserror ('Error inserting into the table', 16, 1)
rollback transaction test
return -100
end
else
begin
exec (@vcQuery)
commit transaction test
end
set quoted_identifier off
end
here i want to store date in the form of smalldate ie., 19/12/2003 for columns startdate and enddate which are defined as datetime datatypes. I am concatenating the values with a plus symbol in the specified varchar local variable.
Any help would be appreciated.
Other details
=============
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
December 22, 2003 at 2:47 am
If you want to remove the time portion from input dates then cast them to varchar(11). Is this the whole procedure? The reason I ask is that I question the logic and the purpose of using a transaction.
I would write the procedure thus
set quoted_identifier off
set nocount on
declare@intError int
If not exists (selecting whether that same job is existing with the same date)
begin
Raiserror ('Not valid data', 16, 1)
return -100
end
insert into tableA (jobname, jobstatus, startdate, enddate, location, created_date)
values (@in_vcjobname,
convert(varchar, @in_intjobstatus),
cast(@in_dtstartdate as varchar(11),
cast(@in_dtenddate as varchar(11),
@in_vclocation,
gedtate())
set @intError = @@error
if (@intError <> 0)
begin
Raiserror ('Error inserting into the table', 16, 1)
rollback transaction test
return -100
end
set quoted_identifier off
Far away is close at hand in the images of elsewhere.
Anon.
December 22, 2003 at 6:39 am
Hi David,
Thank you for replying. But, i have a small doubt with respect to the creation of the table itself. Should i go ahead having the datatype as varchar for the respective date column in the design itself or should it be only datetime, because you have casted the datetime to varchar(11).
I am new to this area, and wanted to know whether the logic which i have written is ok as you might have worked on different projects. How to dynamically frame the query in this scenario? i want to have some tips from you as a SQL Guru.
December 23, 2003 at 2:17 am
When designing a table, if a column is going to contain a date then I automatically make it datetime. There are a lot of pros and cons on what dataype to use. There is nothing wrong in using varchar other than the format of the data is unknown (unlike datetime). It all depends on your applications / presentation levels and business rules. In your example, you posted, you are supplying two datetime parameters and I assumes that they might contain time, that is why I used cast, otherwise if there is not time portion (ie the time value is 00:00:00) then you can match the variables directly, as in
insert into tableA
(jobname, jobstatus, startdate, enddate, location, created_date)
values (@in_vcjobname,
convert(varchar, @in_intjobstatus),
@in_dtstartdate,
@in_dtenddate,
@in_vclocation,
gedtate())
As for the building of a varchar for executing then you need to cast the dates for concatenation, as in
set @vcQuery = 'insert into tableA
(jobname, jobstatus, startdate, enddate, location, created_date)
values ('
set @vcQuery = @vcQuery + "'" + @in_vcjobname + "'," +
"'" + cast(@in_intjobstatus as varchar) + "'," +
"'" + cast(@in_dtstartdate as varchar(11)) + "'," +
"'" + cast(@in_dtenddate as varchar(11)) + "'," +
"'" + @in_vclocation + "'," +
"'" + gedtate()) + "'"
This will produce the dates in 'mmmm dd yyyy' format.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply