Using dates in SPs

[Guest_imported]

I need to get the system date (from unix) into a field in a table from within an SP.
I have done this before OK in a shell script, but how do you pass $date once you have create stored procedure ?
insert into tablename values (....., $date or "$date" ) doesn't work.
Thanks in advance.

 

[qyllr]

hello,

could you not use the getdate() function instead of passing in the date?

insert tableName
values (...,getdate())
go

q.

 

[Guest_imported]

Thanks qyllr

But when I use
declare @v_month numeric
and
select @v_month = (select datepart(month,getdate()))

I get 7 for July, when I need 07 for my 2-digit month
field in my table.

 

[qyllr]

hello,

unless you do an if statement that will add the zeroes... sybase will only return the value without the leading zero...

if you are ultimately trying to insert the date into your table...

try this instead...

select convert(char(8),getdate(),112)

hth,
q.

 

[grega]

You could try

declare @v_month char(2)

select @v_month = case
when datepart(month,getdate()) < 10
then &quot;0&quot; + convert(char(1),datepart(month,getdate()))
else convert(char,datepart(month,getdate()))
end

Greg.

 

[Guest_imported]

This gives the date in CCYYMMDDHHMMSS00 format :-

create procedure spname
as
declare @v_yr numeric
declare @v_tm numeric

select @v_yr = convert(numeric(16),(select convert(char(16),getdate(),112)))

select @v_tm =
(convert(numeric(8),substring((select convert(char(8),getdate(),108)),1,2)+substring((select convert(char(8),getdate(),108)),4,2)+substring((select convert(char(8),getdate(),108)),7,2)) * 100)
select (@v_yr * 100000000)+@v_tm

select @v_yr