Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70

    Unanswered: Date conversion ?

    Is use this stored procedure.
    This is the error mesage: "Syntax error converting datetime from character string"

    Please help me !

    Alter Procedure "Selectie_Date_Tabel" (@datainceput datetime, @datasfirsit datetime,@Grupa AS nvarchar(20))

    As

    set nocount on

    DECLARE @NEWLINE AS char(1)

    SET @NEWLINE = CHAR(10)

    DECLARE @keyssql AS varchar(1000)


    SET @keyssql = 'SELECT * FROM View2'
    + @NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @Grupa + CHAR(39)
    + @NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @datainceput , 120) + ' AND ' + CONVERT(DATETIME, @datasfirsit , 120) +')'

    EXEC (@keyssql)

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    What parameters are you using to execute the stored procedure ???
    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    @datainceput DATETIME
    @datasfirsit DATETIME

    datainceput = 01.01.2004
    datasfirsit = 15.01.2004

    I want to make a SQL_String something like this :



    SQL_String = 'SELECT * FROM TABLE WHERE ' ..... date condition

    EXECUTE (SQL_String)

    All this inside a stored procedure

    Sorry for my english

  4. #4
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    I have two option

    1. Sp_1

    SELECT * FROM TABLE WHERE ................

    Is ok, work

    2. Sp_2

    DECLARE @keyssql AS varchar(8000)
    SET @keyssql ='SELECT * FROM TABLE WHERE' + 'Condition'

    EXECUTE (@keyssql) ---- This line is inside at the same stored procedure.

    This stored procedure Sp_2 don`t work

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Enjoy ...

    Code:
    Alter Procedure "Selectie_Date_Tabel" (@datainceput datetime, @datasfirsit datetime,@Grupa AS nvarchar(20))
    
    As
    
    set nocount on
    
    DECLARE @NEWLINE AS char(1)
    
    SET @NEWLINE = CHAR(10)
    
    DECLARE @keyssql AS varchar(1000)
    
    
    SET @keyssql = 'SELECT * FROM View2'
    + @NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @Grupa + CHAR(39)
    + @NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @datainceput , 104) + ' AND ' + CONVERT(DATETIME, @datasfirsit , 104) +')' 
    
    EXEC (@keyssql)
    Get yourself a copy of the The Holy Book

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

  6. #6
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    ALTER PROCEDURE SP_2
    As
    set nocount on

    DECLARE @keyssql AS varchar(8000)

    SET @keyssql = 'SELECT * FROM View2 WHERE (Data = CONVERT(DATETIME,' +CHAR(39)+ '2004-01-05 00:00:00'+CHAR(39)+', 102))'

    EXEC (@keyssql)
    /*---------------------------------------------*/

    This SP work OK.


    I want to use a parameter inside '2004-01-05 00:00:00'

    Atention EXEC (@keyssql) is inside a SP

  7. #7
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    Enigma, Sorry don't work ..........

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    didnt get that !!! did the sp not work ???
    Get yourself a copy of the The Holy Book

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

  9. #9
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    This is the original SP
    But an solution for the precedent example it would usefull for me.

    Alter Procedure sp_CrossTab
    @table AS sysname,
    @onrows AS nvarchar(128),
    @onrowsalias AS sysname = NULL,
    @oncols AS nvarchar(128),
    @sumcol AS sysname = NULL,
    @avgcol AS sysname = NULL,
    @Grupa AS nvarchar(20),
    @datainceput AS datetime,
    @datasfirsit AS datetime

    AS
    set nocount on

    DECLARE @sql AS varchar(8000), @NEWLINE AS char(1)

    SET @NEWLINE = CHAR(10)

    SET @table = '['+ @table + ']'
    SET @oncols = '['+ @oncols + ']'
    SET @onrows = '['+ @onrows + ']'
    SET @sumcol = '['+ @sumcol + ']'
    SET @avgcol = '['+ @avgcol + ']'


    SET @sql ='SELECT' + @NEWLINE
    + 'DATEPART(ww,' + @onrows+ ') AS Saptamina,' + ' '
    + 'DATEPART(mm,' + @onrows+ ') AS Luna,' + ' '
    + 'DATEPART(yyyy,' + @onrows+ ') AS Anul,' + ' '
    + @onrows +

    CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
    END

    CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

    DECLARE @keyssql AS varchar(1000)

    /* THIS PART DON'T WORK */

    SET @keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +'FROM ' + @table
    + @NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @Grupa + CHAR(39)
    + @NEWLINE + 'AND ([Data] BETWEEN ' + CONVERT(DATETIME, @datainceput , 120) +' AND ' +CONVERT(DATETIME, @datasfirsit , 120) +')'

    /* THIS PART WORK OK*/
    /*SET @keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +'FROM ' + @table*/

    PRINT @keyssql

    EXEC (@keyssql)


    DECLARE @key AS nvarchar(100)
    SELECT @key = MIN(keyvalue) FROM #keys

    WHILE @key IS NOT NULL
    BEGIN
    ....................................

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Use This >>>>
    SET @keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +'FROM ' + @table
    + @NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @Grupa + CHAR(39)
    + @NEWLINE + 'AND ([Data] BETWEEN ' + CONVERT(DATETIME, @datainceput , 104) +' AND ' +CONVERT(DATETIME, @datasfirsit , 104) +')'
    Get yourself a copy of the The Holy Book

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

  11. #11
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    I try but the same error: "Error converting data type varchar to datetime"

    Please help me,
    Try an easy example:
    One table with 3 columns (Day, Field1, Field2)
    Create a SP and see if work.

  12. #12
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    maybe i am not understanding the dateformat you are passing ...

    check up convert in the holy book ("SQL Server Books Online") and insert the no corresponding to your input format
    Get yourself a copy of the The Holy Book

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

  13. #13
    Join Date
    Sep 2003
    Posts
    39
    I have a strong suspicion that the '15.01.2004' date is causing the problem. Whereever possible, you should feed 'yyyy-mm-dd' strings to the sql server. If you can't, then you need to declare your parameters as STRING instead of datetime since 15.01.2004 may not be a date if the date format on the Sql Server is set to 'mm-dd-yyyy' There is no 15th month (not on Earth, anyway ).

Posting Permissions

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