Creating a strange stored procedure involving dates 1

[abienz]

Hi there,

I need to create a stored procedure that inserts into a table with only about 5 different fields.

2 Fields I can hardcode, as these will not change, but the other three will change each time the SP is run, they are Value, Date, and Time.

Value can be determined from a select statement...

SELECT (SUM(Price)/17)*1.886 AS Value
FROM v_Search

Date and Time however will be a bit more tricky I feel, because these two values aren't stored as Datetime datatypes, but instead as Char.

Date is a Char of 8 characters, and Time is 6
typical values for these two fields are 20010520 for date, and 191510 for time, I'm sure you can see how these may have been derived. YYYYMMDD and HHMMSS

Anyway back to my problem. I need a stored procedure that doesn't take any input except from other tables, and that can dynamically generate the date and time in the above format to insert into the target table

Any clues will be much appreciated

Cheers,
Alex.

 

[RickCole]

Try the following:

select convert(varchar(8),getdate(),112) as date
select replace(convert(varchar(8),getdate(),108), ':','') as time


Rick.

 

[abienz]

That's Great! I've now managed to gormat my date and times correctly, but I now have another issue.

Here's my SP so far...

SELECT (SUM(Price)/17)*1.886 AS Valueb, convert(varchar(8),getdate(),112) AS datee, replace(convert(varchar(8),getdate(),108), ':','') as timee
FROM v_Search
WHERE EpicCode = 'WBA'
OR EpicCode = 'AFC'
OR EpicCode = 'ASV'

INSERT INTO stp_footiehist Values ('FOT', 'C', Valueb, datee, timee)

The SELECT statement returns to me three values, which is what I want, and I want to be able to plave these values into the target table, using the INSERT INTO statement.

However this doesn't work because I am using Column names and SQL doesn't allow that, which I can understand under normal circumstances. How can I take the results from my select statement and insert them into my target table?

Am I doing this the wrong way perhaps?

 

[RickCole]

Try this:

INSERT INTO stp_footiehist(col1,col2,col3,col4,col5)

SELECT ('FOT', 'C', SUM(a.Price)/17)*1.886, convert(varchar(8),getdate(),112), replace(convert(varchar(8),getdate(),108), ':','')
FROM v_Search a
WHERE EpicCode = 'WBA'
OR EpicCode = 'AFC'
OR EpicCode = 'ASV'


Where the column names col1 to col5 = the column names you are inserting into.