none
error con with RRS feed

  • Pregunta

  • Saludos llevo un rato tratando de solucionar este problema de sintaxis y no doy con que pueda ser

    DECLARE @y smallint, @m tinyint, @uid int;
    DECLARE @dt smalldatetime;
    SELECT @y = 2010, @m = 6, @uid = 1;
    SELECT @dt = CAST(@y AS char(4)) + RIGHT('00' + LTRIM(@m), 2) + '01' ;
    WITH mes_dias AS (
    SELECT @dt AS dt
    UNION ALL
    SELECT DATEADD([day], 1, dt)
    FROM mes_dias 
    WHERE DATEADD([day], 1, dt) < DATEADD([month], 1, @dt)
    )
    SELECT mes_dias

    me vota

    Msg 156, Level 15, State 1, Line 6

    Incorrect syntax near the keyword 'WITH'.

    viernes, 24 de agosto de 2012 21:01

Respuestas

  • Solo para estar seguros, esto es lo que estoy haciendo, sin recibir error:

    DECLARE @y smallint, @m tinyint, @uid int;
    DECLARE @dt smalldatetime;
    SELECT @y = 2010, @m = 6, @uid = 1;
    SELECT @dt = CAST(@y AS char(4)) + RIGHT('00' + LTRIM(@m), 2) + '01' ;
    WITH mes_dias AS (
    SELECT @dt AS dt
    UNION ALL
    SELECT DATEADD([day], 1, dt)
    FROM mes_dias 
    WHERE DATEADD([day], 1, dt) < DATEADD([month], 1, @dt)
    )
    select * from mes_dias


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    viernes, 24 de agosto de 2012 21:55
  • Hola

    prueba con este query.

    --Declarar variables
    DECLARE @y smallint, @m tinyint, @uid int;
    DECLARE @dt smalldatetime;
    --asignar valores
    SELECT @y = 2010, @m = 6, @uid = 1;
    SELECT @dt = CAST(@y AS char(4)) + RIGHT('00' + LTRIM(@m), 2) + '01' ;
    --CTE
    WITH mes_dias (dt)
    AS (
    		SELECT @dt AS dt
    	UNION ALL
    		SELECT DATEADD([day], 1, dt)
    		FROM mes_dias 
    		WHERE DATEADD([day], 1, dt) < DATEADD([month], 1, @dt)
    )
    --Prueba query
    select *
    from mes_dias;

    Culaquier problema puedes contactarte con migo

    Saludos cordiales


    Ahias Portillo

    sábado, 25 de agosto de 2012 20:37

Todas las respuestas

  • Estas tratando de seleccionar de el CTE que haz creado? La ultima linea deberia ser SELECT <col>,... from mes_dias

    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    viernes, 24 de agosto de 2012 21:22
  • da el mismo error aunque lo quite

    DECLARE @y smallint, @m tinyint, @uid int;
    DECLARE @dt smalldatetime;
    SELECT @y = 2010, @m = 6, @uid = 1;
    SELECT @dt = CAST(@y AS char(4)) + RIGHT('00' + LTRIM(@m), 2) + '01' ;
    WITH mes_dias AS (
    SELECT @dt AS dt
    UNION ALL
    SELECT DATEADD([day], 1, dt)
    FROM mes_dias 
    WHERE DATEADD([day], 1, dt) < DATEADD([month], 1, @dt)
    )
    viernes, 24 de agosto de 2012 21:28
  • Esto no me esta dando ningun error.

    Que version de SQL Server estas usando?


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    viernes, 24 de agosto de 2012 21:30
  • sql server 2008 r2
    viernes, 24 de agosto de 2012 21:35
  • Lo he probado en la misma version, y no recibo error.

    Estas usando exactamente el mismo script que escribes aqui? o este es solo un fragmento?


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    viernes, 24 de agosto de 2012 21:42
  • los dos querys tal cual estan publicados en el post me arrojan ese error.

    Saludos.

    viernes, 24 de agosto de 2012 21:50
  • Solo para estar seguros, esto es lo que estoy haciendo, sin recibir error:

    DECLARE @y smallint, @m tinyint, @uid int;
    DECLARE @dt smalldatetime;
    SELECT @y = 2010, @m = 6, @uid = 1;
    SELECT @dt = CAST(@y AS char(4)) + RIGHT('00' + LTRIM(@m), 2) + '01' ;
    WITH mes_dias AS (
    SELECT @dt AS dt
    UNION ALL
    SELECT DATEADD([day], 1, dt)
    FROM mes_dias 
    WHERE DATEADD([day], 1, dt) < DATEADD([month], 1, @dt)
    )
    select * from mes_dias


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    viernes, 24 de agosto de 2012 21:55
  • Quitando el SELECT al final, la consulta no tiene sentido. Necesitas añadir el SELECT tal como te lo explique arriba.

    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    viernes, 24 de agosto de 2012 22:03
  • Gracias por tu paciencia y respuestas,

    en efecto ese mismo código lo he copiado y pegado en mi Sql Server Management Studio y arroja el mismo error.

    viernes, 24 de agosto de 2012 22:03
  • Lamentablemente no puedo reproducir tu problema. Como te dije arriba, ese mismo codigo lo estoy ejecutando sin errores en una instancia de SQL Server 2008 R2, Developer Edition.

    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    viernes, 24 de agosto de 2012 22:06
  • Ok, bueno probaré llegando a casa   y aver que tal.

    Gracias.

    viernes, 24 de agosto de 2012 22:10
  • Hola

    prueba con este query.

    --Declarar variables
    DECLARE @y smallint, @m tinyint, @uid int;
    DECLARE @dt smalldatetime;
    --asignar valores
    SELECT @y = 2010, @m = 6, @uid = 1;
    SELECT @dt = CAST(@y AS char(4)) + RIGHT('00' + LTRIM(@m), 2) + '01' ;
    --CTE
    WITH mes_dias (dt)
    AS (
    		SELECT @dt AS dt
    	UNION ALL
    		SELECT DATEADD([day], 1, dt)
    		FROM mes_dias 
    		WHERE DATEADD([day], 1, dt) < DATEADD([month], 1, @dt)
    )
    --Prueba query
    select *
    from mes_dias;

    Culaquier problema puedes contactarte con migo

    Saludos cordiales


    Ahias Portillo

    sábado, 25 de agosto de 2012 20:37