none
Porque me vota error en las fechas ? RRS feed

  • Pregunta

  • saludos amigos del foro, necesito su ayuda, no soy tan experto en sql server 2012, tengo un problema que me vota error en las fechas como este ejemplo: (afch BETWEEN "01/01/2001" AND "10/08/2016") que es lo que tengo que hacer, si me podrian ayudarme por favor,

    saludos,

    rudolf heiner.

    SELECT cc.*, cc.ctacod as ctacod1, substring(cc.ctacod,1,1) as posi,
    (select sum(adeb) from drio where substring(acntt,1,1) =  substring(cc.ctacod,1,1) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) as debecuen1,
    (select sum(ahb) from drio where substring(acntt,1,1) =  substring(cc.ctacod,1,1) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) as habercuen1,
    case when (select sum(adeb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else
    (select sum(adeb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as debecuen2,
    case when (select sum(ahb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as habercuen2,
    case when (select sum(adeb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else 
    (select sum(adeb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as debecuen3,
    case when (select sum(ahb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as habercuen3,
    case when (select sum(adeb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else 
    (select sum(adeb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as debecuen4,
    case when (select sum(ahb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) is  null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as habercuen4,
    case when (select sum(adeb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else
    (select sum(adeb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as debecuen5,
    CASE WHEN (select sum(ahb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as habercuen5,
    (select sum(adeb) from drio where substring(acntt,1,1) =  substring(cc.ctacod,1,1) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) as debe,
    (select sum(ahb) from drio where substring(acntt,1,1) =  substring(cc.ctacod,1,1) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) as haber,
    case when (select sum(adeb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else
    (select sum(adeb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as debec,
    case when (select sum(ahb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as haberc,
    case when (select sum(adeb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else 
    (select sum(adeb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as debecu,
    case when (select sum(ahb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as habercu,
    case when (select sum(adeb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else 
    (select sum(adeb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as debecue,
    case when (select sum(ahb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) is  null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as habercue,
    case when (select sum(adeb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else
    (select sum(adeb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as debecuen,
    CASE WHEN (select sum(ahb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    anro = 1 and (afch BETWEEN "01/01/2001" AND "10/08/2016")) end as habercuen
    from cnta cc left join drio d on d.acntt = cc.ctacod
    group by cc.ctacod, d.acntt ORDER BY cc.ctacod asc

    miércoles, 10 de agosto de 2016 12:48

Respuestas

  • Rudolf Heiner,

    ¿Qué es lo que no has entendido?

    La lista de columnas por las que agrupas son dos: [cc.ctacod] y [d.acntt]. Pues bien, el conjunto de resultados ha sido agrupado por las columnas mencionadas (claves de agrupación), si deseas mostrar otras columnas -que no sean las claves de agrupación- debes de utilizar alguna función de agregado sobre ellas, como MAX(), MIN(), SUM(), etc., no digo que hagas uso indiscriminado de las funciones, digo que hagas uso de ellas según la forma de los datos que deseas mostrar.

    Dicho lo anterior, la expresión cc.*muestra todas las columnas de la tabla [cnta], y no todas las columnas son claves de agrupación, por tanto te pedí que saques esa referencia de la consulta, quedando la consulta -nuevamente lo menciono- de la siguiente manera:

    SELECT cc.ctacod as ctacod1, <...>


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    • Marcado como respuesta Rudolf Heiner miércoles, 10 de agosto de 2016 22:33
    miércoles, 10 de agosto de 2016 13:47

Todas las respuestas

  • Hola Rudolf Heiner

    Cual es el error que te presenta?

    Saludos


    Javier

    miércoles, 10 de agosto de 2016 12:51
  • Hola Javier gracias por responder, cuando estoy en el editor de consultas del SQL server Express 2012 sale subrayado con rojo todas las fechas, se que hay algo mal pero no se que es.

    quedo de ti,

    saludos,

    rudolf heiner.

    miércoles, 10 de agosto de 2016 12:56
  • Podrías subir una imagen para revisar.

    Javier

    miércoles, 10 de agosto de 2016 12:58
  • Por un lado los literales en SQL Server debes especificarlos con comillas simples, no dobles.

    Por otro, el formato de fechas que debes usar depende de la configuración del SQL Server.

    Para utilizar un formato independiente del idioma del SQL Server puedes usar un formato ISO, bien YYYY-MM-DD o simplemente YYYYMMDD.

    En lugar de

    BETWEEN "01/01/2001" AND "10/08/2016"

    utiliza

    BETWEEN '20010101' AND '20160810'


    Píldoras .NET
    Artículos, tutoriales y ejemplos de código .NET

    Píldoras JS
    Artículos, tutoriales y ejemplos de código JavaScript, HTML5, CSS3, ...

    • Propuesto como respuesta Willams Morales miércoles, 10 de agosto de 2016 13:02
    miércoles, 10 de agosto de 2016 12:59
  • Rudolf Heiner,

    Los literales de fecha deben de estar delimitados por el caracter de apóstrofo (comillas simples ' '). Así mismo, te sugiero que hagas uso de un formato estándar para escribir literales de fecha, como por ejemplo el formato ISO 8601: YYYYMMDD:

    <...>,
    (SELECT SUM(adeb) FROM drio WHERE SUBSTRING(acntt,1,1) = SUBSTRING(cc.ctacod,1,1) 
    AND (afch BETWEEN '20010101' AND '20160810')) AS debecuen1,
    <...>


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    miércoles, 10 de agosto de 2016 13:01
  • Gracias por responder Asier, ya me habia dado cuenta que ese era el error, pero ahora me vota otro error: 

    Mens. 8120, Nivel 16, Estado 1, Línea 1
    La columna 'cnta.ctanom' de la lista de selección no es válida, porque no está contenida en una función de agregado ni en la cláusula GROUP BY.

    SELECT cc.*, cc.ctacod as ctacod1, substring(cc.ctacod,1,1) as posi,
    (select sum(adeb) from drio where substring(acntt,1,1) =  substring(cc.ctacod,1,1) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) as debecuen1,
    (select sum(ahb) from drio where substring(acntt,1,1) =  substring(cc.ctacod,1,1) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) as habercuen1,
    case when (select sum(adeb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else
    (select sum(adeb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as debecuen2,
    case when (select sum(ahb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as habercuen2,
    case when (select sum(adeb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else 
    (select sum(adeb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as debecuen3,
    case when (select sum(ahb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as habercuen3,
    case when (select sum(adeb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else 
    (select sum(adeb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as debecuen4,
    case when (select sum(ahb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) is  null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as habercuen4,
    case when (select sum(adeb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else
    (select sum(adeb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as debecuen5,
    CASE WHEN (select sum(ahb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as habercuen5,
    (select sum(adeb) from drio where substring(acntt,1,1) =  substring(cc.ctacod,1,1) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) as debe,
    (select sum(ahb) from drio where substring(acntt,1,1) =  substring(cc.ctacod,1,1) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) as haber,
    case when (select sum(adeb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else
    (select sum(adeb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as debec,
    case when (select sum(ahb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,3) =  substring(cc.ctacod,1,3) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as haberc,
    case when (select sum(adeb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else 
    (select sum(adeb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as debecu,
    case when (select sum(ahb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,5) =  substring(cc.ctacod,1,5) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as habercu,
    case when (select sum(adeb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else 
    (select sum(adeb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as debecue,
    case when (select sum(ahb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) is  null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,7) =  substring(cc.ctacod,1,7) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as habercue,
    case when (select sum(adeb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else
    (select sum(adeb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as debecuen,
    CASE WHEN (select sum(ahb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) is null then 0 else 
    (select sum(ahb) from drio where substring(acntt,1,9) =  substring(cc.ctacod,1,9) and
    anro = 1 and (afch BETWEEN '01/01/2001' AND '10/08/2016')) end as habercuen
    from cnta cc left join drio d on d.acntt = cc.ctacod
    group by cc.ctacod, d.acntt ORDER BY cc.ctacod asc

    miércoles, 10 de agosto de 2016 13:05
  • Deberías agregar cnta.ctanom en el group by

    Javier

    miércoles, 10 de agosto de 2016 13:06
  • Gracias por responder Willams, ya me habia dado cuenta de eso y ya lo cambie pero ahora me vota otro error:

    Mens. 8120, Nivel 16, Estado 1, Línea 1
    La columna 'cnta.ctanom' de la lista de selección no es válida, porque no está contenida en una función de agregado ni en la cláusula GROUP BY.

    miércoles, 10 de agosto de 2016 13:07
  • Rudolf Heiner,

    La lista de las columnas que ocupas en la clausula <GROUP BY> deben ser las mismas que debes mostrar en la lista de selección <SELECT> -según se requiera-, todas las demás columnas deben de mostrarse con alguna función de agregado, en tu caso, retira la referencia a todas las columnas '*' .

    SELECT cc.ctacod as ctacod1, <...>

    Por otro lado, evalúa sustituir el uso de las subconsultas por combinación, imagino que esa consulta debe de costar mucho en términos de rendimiento.


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    miércoles, 10 de agosto de 2016 13:12
  • no entendi muy bien Willams, sql server para mi es nuevo, me podras ayudar con el codigo por favor...

    saludos,

    rudolf heiner.

    miércoles, 10 de agosto de 2016 13:30
  • Rudolf Heiner,

    ¿Qué es lo que no has entendido?

    La lista de columnas por las que agrupas son dos: [cc.ctacod] y [d.acntt]. Pues bien, el conjunto de resultados ha sido agrupado por las columnas mencionadas (claves de agrupación), si deseas mostrar otras columnas -que no sean las claves de agrupación- debes de utilizar alguna función de agregado sobre ellas, como MAX(), MIN(), SUM(), etc., no digo que hagas uso indiscriminado de las funciones, digo que hagas uso de ellas según la forma de los datos que deseas mostrar.

    Dicho lo anterior, la expresión cc.*muestra todas las columnas de la tabla [cnta], y no todas las columnas son claves de agrupación, por tanto te pedí que saques esa referencia de la consulta, quedando la consulta -nuevamente lo menciono- de la siguiente manera:

    SELECT cc.ctacod as ctacod1, <...>


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    • Marcado como respuesta Rudolf Heiner miércoles, 10 de agosto de 2016 22:33
    miércoles, 10 de agosto de 2016 13:47