Tech Support Guy banner
Status
Not open for further replies.
1 - 7 of 7 Posts

·
Registered
Joined
·
74 Posts
Discussion Starter · #1 ·
I have 2 storeed procedure in SQL Server 7.o. One of them needs the other as in:

-- procedure to change the date format of records in the ClientDate tableCREATE PROCEDURE [sp_ConvertDate] AS DECLARE @monthNumAsString VARCHAR(2), @monthName VARCHAR(3), @clientDate VARCHAR(30), @keyFld INTDECLARE cursCD CURSOR FAST_FORWARD FORSELECT keyFld, clientDate FROM ClientDateOPEN cursCDFETCH NEXT FROM cursCD INTO @keyFld, @clientDateWHILE @@FETCH_STATUS = 0BEGIN -- Convert the month name to a string representing the month number SET @monthName = SUBSTRING(@clientDate, 3,3) EXEC @monthNumAsString = sp_MonthToNumberString @monthName -- Now update the string field in the DB UPDATE ClientDate SET clientDate = SUBSTRING(@clientDate,6,4) + @monthNumAsString + SUBSTRING(@clientDate,1,2) WHERE keyFld = @keyFld FETCH NEXT FROM cursCD INTO @keyFld, @clientDateENDCLOSE cursCD
DEALLOCATE cursCD

-- **************8
-- Procedure to convert a month as 'MMM' to a string value
-- representing the month numberCREATE PROCEDURE [sp_MonthToNumberString] ( @monthName varchar(3) )ASDECLARE @monthNumS varchar(2)IF @monthName = 'JAN' SET @monthNumS ='0 1'ELSE IF @monthName = 'FEB' SET @monthNumS =' 02'ELSE IF @monthName = 'MAR' SET @monthNumS = '03'ELSE IF @monthName = 'APR' SET @monthNumS = '04'ELSE IF @monthName = 'MAY' SET @monthNumS = '05'ELSE IF @monthName = 'JUN' SET @monthNumS = '06'ELSE IF @monthName = 'JUL' SET @monthNumS = '07'ELSE IF @monthName = 'AUG' SET @monthNumS = '08'ELSE IF @monthName = 'SEP' SET @monthNumS = '09'ELSE IF @monthName = 'OCT' SET @monthNumS = '10'ELSE IF @monthName = 'NOV' SET @monthNumS = '11'ELSE IF @monthName = 'DEC' SET @monthNumS = '12'ELSE SET @monthNumS = '00'RETURN @monthNumS

How do I go about triggering them?

What I am trying to do is to convert the date field "01DEC1993:09:57:32:49" to say "19931201"

monthNumAsString monthName clientDate keyFld
---------------- --------- ------------------------------ -----------
01 JAN 01DEC1993:09:57:32:49 1
01 JAN 01JAN1993:09:57:32:49 2
01 JAN 01FEB1993:09:57:32:49 2
01 JAN 01MAR1993:09:57:32:49 2
01 JAN 01APR1993:09:57:32:49 2

(5 row(s) affected)

Wango
 

·
Registered
Joined
·
21,334 Posts
What kind of front end are you using? If it's in ASP do something like this:

set connection = server.createobject("adodb.connection")
connection.open whateverDSN
Connection.Execute "ProcNamee varvalue1, varvalue2"
 

·
Registered
Joined
·
74 Posts
Discussion Starter · #3 ·
At one time I thought (wrongly may be and before I ever attempted to run one) that I can run stored procedures in say, the SQL Server query builder. If I cannot, then I can build something in C/C++ very quickly.

The question still remains, How do I run the main procedure [sp_ConvertDate] when it depends on another one
[sp_MonthToNumberString] ?

Wango
 

·
Registered
Joined
·
21,334 Posts
Seems like an awefully conveluded way to get a date formatted that way. WHat does the date format look like in the table itself when it is entered and what format is it in once it is converted into your custom string.
 

·
Registered
Joined
·
74 Posts
Discussion Starter · #5 ·
As was mentioned in the original thread,

********************************************
What I am trying to do is to convert the date field "01DEC1993:09:57:32:49" to say "19931201"
********************************************

the date looks like say,

"01DEC1993:09:57:32:49"

and the customer wants it to look like

"19931201"

Wango
 

·
Registered
Joined
·
21,334 Posts
Why don't you use an Trim function to first trim everything off to the right of the colon and then work with what's left over and it could all be done from one stored procedure.
 

·
Registered
Joined
·
74 Posts
Discussion Starter · #7 ·
I think you have a point; however there is more than one way to skin "Silvester". I have just run the "sp_ConvertDate" stored procedure form the "SQL Server Query Analyser" (straight in the database environment". I started out with:

monthNumAsString clientDate keyFld
---------------- ------------------------------ -----------
1 19931211 11
2 12DEC1993:09:57:32:49 12
3 13DEC1993:09:57:32:49 13
4 14DEC1993:09:57:32:49 14
5 15DEC1993:09:57:32:49 15
6 16DEC1993:09:57:32:49 16
7 17DEC1993:09:57:32:49 17
8 18DEC1993:09:57:32:49 18
9 19DEC1993:09:57:32:49 19
10 20DEC1993:09:57:32:49 20

I ended up with:

monthNumAsString clientDate keyFld
---------------- ------------------------------ -----------
1 19931211 11
2 19931212 12
3 19931213 13
4 19931214 14
5 19931215 15
6 19931216 16
7 19931217 17
8 19931218 18
9 19931219 19
10 19931220 20

(10 row(s) affected)

So, I guess the stored procedure that I am using works well. All my method is doing is to extract only what it needs from the long
"02DEC1993:09:57:32:49" [ClientDate] attribute.

If you have a more effient way, I will be glad to try it.

Rockn, what happened to the cascading combo box that you promised me!

Thanks and happy holidays,

Wango
 
1 - 7 of 7 Posts
Status
Not open for further replies.
Top