Snitz Forums 2000 - Snitz Time in a Stored Procedure

Snitz Forums 2000

Username:	Password:
Save Password
Forgot your Password?

All Forums

Help Groups for Snitz Forums 2000 Users

Help: Database: MS SQL Server

Snitz Time in a Stored Procedure

New Topic

Topic Locked

Printer Friendly

Author

Topic

Podge
Support Moderator

Ireland
3776 Posts

Posted - 13 September 2005 : 13:03:38

I cannot pass the time in Snitz format to an sp so I must calculate snitz time within it.

Whats wrong with this?

CREATE PROCEDURE [dbo].[snitz_time]
@SnitzTime varchar(14)
AS

@snitzTime = year + month + day
print @snitzTime
GO

I realise the hour, minutes and seconds have to be added also.

Podge.

The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)

My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board

Warning: The post above or below may contain nuts.

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 13 September 2005 : 17:49:44

You need to change the statement that sets the value for @snitztime:


SET @snitzTime = Year(getdate()) + Month(getdate()) + Day(getdate())

Any, this is just to show you the syntax error, this won't give you the date in Snitz format.

Snitz 3.4 Readme | Like the support? Support Snitz too

Podge
Support Moderator

Ireland
3776 Posts

Posted - 13 September 2005 : 18:14:40

It will suit my purposes for the time being. What I'm trying to do this time is this;

A backup job runs daily at 2am. I have it setup so that it sends me an email before it starts and after it finishes.
I want to add steps so that just before backups start it posts a topic in a forum stating that backups have started.
After they are completed I want to reply to the topic stating that they are finished.

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 13 September 2005 : 18:49:56

So where do you need the time?

Snitz 3.4 Readme | Like the support? Support Snitz too

Podge
Support Moderator

Ireland
3776 Posts

Posted - 13 September 2005 : 19:11:31

I need it to insert into the forum_topics row

I think this works

declare @snitztime varchar(14)
DECLARE @DT DATETIME
SET @DT = getdate()
set @snitztime = REPLACE(CONVERT(CHAR(16),@DT,120),'-','')
set @snitztime = replace(@snitztime, ' ', '')
set @snitztime = replace(@snitztime, ':', '')
print @snitztime

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 13 September 2005 : 19:31:46

It works ... but you're missing the seconds.

Snitz 3.4 Readme | Like the support? Support Snitz too

Podge
Support Moderator

Ireland
3776 Posts

Posted - 13 September 2005 : 19:40:06

Actually it doesn't include seconds but that doesn't matter. Have it working fully now.

Have a difficult one for you now.

I want to build a stored procedure which will do the following;
Calculate the total members & total posts only knowing that the table name contains "_members" or "_topics"
(there are multiple members and topics tables and it cannot be done using dynamic sql as it will exceed the operand limit)

I plan to schedule the sp once per day and update a table with the results.