how to convert the human readable date string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to convert the human readable date string

Author

Topic

bmistry
Starting Member

17 Posts

Posted - 2006-01-20 : 10:40:03

Hiya

I'm trying to run the below but getting errors on the convert statement, can anyone point me in the right direction?

exec PurgeRevisionsByDate convert(datetime,'01/01/2005',103)

Error message

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'convert'.

Thanks
Bav

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-20 : 10:46:54

declare @dt datetime
select @dt = convert(datetime,'01/01/2005',103)
exec PurgeRevisionsByDate @dt

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bmistry
Starting Member

17 Posts

Posted - 2006-01-20 : 10:51:58

In my script I did run the below:

DECLARE @dateCurrent datetime
select @dateCurrent = GETDATE()

if @RevisionDate > @dateCurrent
set @RevisionDate = @dateCurrent

I have just ran your bit and it says:
Server: Msg 208, Level 16, State 1, Procedure PurgeRevisionsByDate, Line 19
Invalid object name 'Utility'.

bmistry
Starting Member

17 Posts

Posted - 2006-01-20 : 10:54:03

This is my script:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name =
'PurgeRevisionsByDate')
BEGIN
PRINT 'Dropping Procedure PurgeRevisionsByDate'
DROP Procedure dbo.PurgeRevisionsByDate
END

GO

PRINT 'Creating Procedure PurgeRevisionsByDate'
GO

create procedure dbo.PurgeRevisionsByDate
(
@RevisionDate datetime
)
with encryption
as
/**
@exception - @RevisionDate value is earlier than the last
revision purge
*/
declare @LastPurgeDate datetime
declare @SQL varchar(255)

set nocount on

/*Verify whether the RevisionDate value is earlier than the last
revision purge.*/
select @LastPurgeDate = LastRevisionPurge from Utility

if @LastPurgeDate is not null and @LastPurgeDate >=
@RevisionDate
return 1 /*The RevisionDate value is earlier than
the last revision purge.*/

create table #Revisions
(
Id int not null
)

insert into #Revisions
select ID from Node where Type != 256 AND ArchivedWhen <=
@RevisionDate
UNION
Select ID From Node where Type = 256 AND NodeGUID not in
(select NodeGUID from NodeResource where NodeGUID is not null)
AND ArchivedWhen <= @RevisionDate

DELETE FROM NodeProperty WHERE NodeId in (SELECT Id FROM
#Revisions)
DELETE FROM NodeRole WHERE NodeId in (SELECT Id FROM
#Revisions)
DELETE FROM NodeResource WHERE NodeId in (SELECT Id FROM
#Revisions)
DELETE FROM NodePlaceholderContent WHERE NodeId in (SELECT Id
FROM #Revisions)
DELETE FROM NodePlaceholder WHERE NodeId in (SELECT Id FROM
#Revisions)
DELETE FROM LayoutProperty WHERE NodeId in (SELECT Id FROM
#Revisions)
DELETE FROM NodeLayout WHERE NodeId in (SELECT Id FROM
#Revisions)
DELETE FROM UserRoleMember WHERE NodeId in (SELECT Id FROM
#Revisions)
DELETE FROM NodeLock WHERE NodeId in (SELECT Id FROM
#Revisions)
DELETE FROM Node WHERE Id in (SELECT Id FROM #Revisions)

DECLARE @dateGMT datetime, @dateCurrent datetime
select @dateCurrent = GETDATE()

if @RevisionDate > @dateGMT
set @RevisionDate = @dateGMT

if exists (select * from Utility )
update Utility set LastRevisionPurge = @RevisionDate
else
insert into Utility (LastRevisionPurge)
values(@RevisionDate)

return 0
go

GRANT EXEC ON dbo.PurgeRevisionsByDate TO dbo

GO

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-20 : 11:35:50

>> The third parameter is to convert TO a string FROM a datetime. It isn't used the other way round. For that you should use
That is incorrect - it is also used to convert from string to datetime.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-01-20 : 11:44:56

"That is incorrect - it is also used to convert from string to datetime"

Well I never, I've always thought otherwise.

BoL says (referring to the third parameter):

"Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types)."

which is, of course, silent on converting FROM string TO date.

However, on reading more carefully I see that the table of formats has a little "**" next to it, and the footnote says

"** Input when converting to datetime; output when converting to character data."

Thanks for spotting and pointing that out Nigel.

Kristen

bmistry
Starting Member

17 Posts

Posted - 2006-01-23 : 04:53:08

So what do I need to do in order to get this script to run correctly? I havent done much work on sqlserver so I'm a bit confused.

bmistry
Starting Member

17 Posts

Posted - 2006-01-24 : 04:17:04

Can anyone help????????????????????????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 04:37:56

Try this : enter the date in YYYY-MM-DD

exec PurgeRevisionsByDate '2005-01-01'

----------------------------------
'KH'
I do work from home but I don't do homework

bmistry
Starting Member

17 Posts

Posted - 2006-01-24 : 04:43:48

I got the below error message:

Server: Msg 208, Level 16, State 1, Procedure PurgeRevisionsByDate, Line 19
Invalid object name 'Utility'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 05:15:22

the table Utility does not exists in the database ?

----------------------------------
'KH'
I do work from home but I don't do homework

bmistry
Starting Member

17 Posts

Posted - 2006-01-24 : 05:27:58

You are right I have just checked and the table is not there. Can that error be ignored? Do I re run the above script but take out:

if exists (select * from Utility )
update Utility set LastRevisionPurge = @RevisionDate
else
insert into Utility (LastRevisionPurge)
values(@RevisionDate)

return 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 05:44:40

>> Can that error be ignored?
>> Do I re run the above script but take out
I am afraid you are the only person that can answer these question.

----------------------------------
'KH'
I do work from home but I don't do homework

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources