Results 1 to 9 of 9

Thread: Syntax Error

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Syntax Error

    Hi All,

    When I run the following portion of my stored procedure, I get the following error message:

    Server: Msg 241, Level 16, State 1, Line 1
    Syntax error converting datetime from character string.

    Here is the script:

    set @dttimestr = 'select @dttime = substring(convert(varchar(11),getdate(),120),1,4)+
    substring(convert(varchar(11),getdate(),120),5,3)'

    EXECUTE sp_executesql @dttimestr, N'@dttime datetime output', @dttime output
    print @dttime

    Can you please tell me how to fix it?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Perhaps

    Code:
    DECLARE @dttimestr nvarchar(4000), @dttime datetime
    
    set @dttimestr = 'select substring(convert(varchar(11),getdate(),120),1,4)+ 
    substring(convert(varchar(11),getdate(),120),5,3)' 
    
    SELECT @dttimestr
    
    EXECUTE sp_executesql @dttimestr, N'@dttime datetime output', @dttime output
    
    print @dttime
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    I've tried that. When I execute this store procedure here are the results:

    (No column name)
    select substring(convert(varchar(11),getdate(),120),1,4)+
    substring(convert(varchar(11),getdate(),120),5,3)'

    (No column name)
    2007-02

    Any ideas?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It would be easier to figure it out if you told us what results you are looking for.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can replace this --

    substring(convert(varchar(11),getdate(),120),1,4)+
    substring(convert(varchar(11),getdate(),120),5,3)

    with this --

    substring(convert(varchar(11),getdate(),120),1,7)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2004
    Posts
    268
    Here is the stored procedure that I am executing:

    CREATE PROCEDURE usp_Populate_DatabaseOriginalSize

    AS

    DECLARE @dbsize DEC(15,2)
    DECLARE @logsize DEC(15,2)
    DECLARE @dbname SYSNAME
    declare @dbsizestr nvarchar(500)
    declare @logsizestr nvarchar(500)
    declare @totaldbsize dec(15,2)
    declare @dbid smallint
    declare @dttime datetime
    declare @dttimestr nvarchar(500)



    DECLARE dbnames_cursor CURSOR
    FOR
    SELECT name, dbid
    FROM dbo.sysdatabases
    ORDER BY name

    OPEN dbnames_cursor

    FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
    WHILE @@FETCH_STATUS = 0
    BEGIN


    SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size))
    FROM ' + @dbname + '.dbo.sysfiles
    WHERE fileid = 1'

    EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output
    PRINT @dbsize

    SET @logsizestr = 'SELECT @logsize = sum(convert(dec(15,2),size))
    FROM ' + @dbname + '.dbo.sysfiles
    WHERE fileid = 2'


    EXECUTE sp_executesql @logsizestr, N'@logsize decimal(15,2) output', @logsize output
    PRINT @logsize

    SET @totaldbsize = LTRIM(STR((@dbsize + @logsize)*8/1024,15,2))
    print @totaldbsize

    set @dttimestr = 'select substring(convert(varchar(11),getdate(),120),1,4) + substring(convert(varchar(11),getdate(),120),5,3)'
    set @dttimestr = 'select substring(convert(varchar(11),getdate(),120),1,4) + substring(convert(varchar(11),getdate(),120),5,3)'
    select @dttimestr

    EXECUTE sp_executesql @dttimestr, N'@dttime datetime output', @dttime output

    print @dttime

    --insert into databaseoriginalsize (dbid, dbname, dbsize, updatedate) values (@dbid, @dbname, @totaldbsize, @dttime)

    FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid

    END


    The results I am looking for are:
    dbid dbname totaldbsize dttime
    25 DB 25.10 2007-02

    I hope this is clear.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you hope it is clear, but you did not ask a question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2004
    Posts
    268
    Here is the question:

    When I run this stored procedure, I get the following error message:

    Server: Msg 241, Level 16, State 1, Line 1
    Syntax error converting datetime from character string.

    I've tried to substitute

    substring(convert(varchar(11),getdate(),120),1,4)+
    substring(convert(varchar(11),getdate(),120),5,3)

    with this --

    substring(convert(varchar(11),getdate(),120),1,7)

    as you suggested, but I am still getting this error. How can I fix it?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pull out the sql statement that you are trying to execute, and run it in Query Manager

    when i run this --
    Code:
    select substring(convert(varchar(11),getdate(),120),1,7)
    i get this --
    Code:
    Column1
    2007-02
    if you get the same thing, then that means that the error message is coming from somewhere else in your procedure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •