Case n�o reconhece campo Hora convertido do campo DataHora

14/01/2022

Bom Dia ,,uma ajuda iniciante perdido
Converti o campo DataHora obtendo o resultado que preciso no campo Hora, mas n�o consigo utilizar o resulta para criar as informa��es na coluna PERIODO, retorna resultado ( Msg 207, Level 16, State 1, Line 5
Nome de coluna 'Hora' inv�lido.)

SELECT
[DataHora],
Convert (nvarchar (5), CONVERT (time,DataHora, 103))Hora,

CASE WHEN Hora BETWEEN '00:01' AND '06:00' THEN 'Das 00:01 �s 06:00'
WHEN Hora BETWEEN '06:01' AND '12:00' THEN 'Das 06:01 �s 12:00'
WHEN Hora BETWEEN '12:01' AND '18:00' THEN 'Das 12:01 �s 18:00'
WHEN Hora BETWEEN '18:01' AND '23:59' THEN 'Das 18:01 �s 23:59'
WHEN Hora is null THEN 'INCERTO'
WHEN Hora = '00:00' THEN 'INCERTO'
END PERIODO

FROM [GEOEST].[dbo].[VW_GeoRacOcr]

Edivaldo Souza

Curtir t�pico + 0

Responder

Posts

14/01/2022

Emerson Nascimento

o campo virtual Hora s� � vis�vel ap�s obter o resultset.
pode ser usado assim:

SELECT
	[DataHora],
	CASE	WHEN Hora BETWEEN '00:01' AND '06:00' THEN 'Das 00:01 �s 06:00'
			WHEN Hora BETWEEN '06:01' AND '12:00' THEN 'Das 06:01 �s 12:00'
			WHEN Hora BETWEEN '12:01' AND '18:00' THEN 'Das 12:01 �s 18:00'
			WHEN Hora BETWEEN '18:01' AND '23:59' THEN 'Das 18:01 �s 23:59'
	ELSE
		'INCERTO'
	END PERIODO
FROM
(
	SELECT
		[DataHora],
		CONVERT(VARCHAR(5), [DataHora], 108) Hora
	FROM
		[GEOEST].[dbo].[VW_GeoRacOcr]
) TEMP

da forma como voc� montou, numa �nica instru��o, � necess�rio repetir a convers�o do campo:

SELECT
	[DataHora],
	CASE
		WHEN CONVERT(VARCHAR(5), [DataHora], 108) BETWEEN '00:01' AND '06:00' THEN 'Das 00:01 �s 06:00'
		WHEN CONVERT(VARCHAR(5), [DataHora], 108) BETWEEN '06:01' AND '12:00' THEN 'Das 06:01 �s 12:00'
		WHEN CONVERT(VARCHAR(5), [DataHora], 108) BETWEEN '12:01' AND '18:00' THEN 'Das 12:01 �s 18:00'
		WHEN CONVERT(VARCHAR(5), [DataHora], 108) BETWEEN '18:01' AND '23:59' THEN 'Das 18:01 �s 23:59'
	ELSE
		'INCERTO'
	END PERIODO
FROM
	[GEOEST].[dbo].[VW_GeoRacOcr]

Responder

Gostei + 0

14/01/2022

Edivaldo Souza

o campo virtual Hora s� � vis�vel ap�s obter o resultset.
pode ser usado assim:

SELECT
	[DataHora],
	CASE	WHEN Hora BETWEEN '00:01' AND '06:00' THEN 'Das 00:01 �s 06:00'
			WHEN Hora BETWEEN '06:01' AND '12:00' THEN 'Das 06:01 �s 12:00'
			WHEN Hora BETWEEN '12:01' AND '18:00' THEN 'Das 12:01 �s 18:00'
			WHEN Hora BETWEEN '18:01' AND '23:59' THEN 'Das 18:01 �s 23:59'
	ELSE
		'INCERTO'
	END PERIODO
FROM
(
	SELECT
		[DataHora],
		CONVERT(VARCHAR(5), [DataHora], 108) Hora
	FROM
		[GEOEST].[dbo].[VW_GeoRacOcr]
) TEMP

da forma como voc� montou, numa �nica instru��o, � necess�rio repetir a convers�o do campo:

SELECT
	[DataHora],
	CASE
		WHEN CONVERT(VARCHAR(5), [DataHora], 108) BETWEEN '00:01' AND '06:00' THEN 'Das 00:01 �s 06:00'
		WHEN CONVERT(VARCHAR(5), [DataHora], 108) BETWEEN '06:01' AND '12:00' THEN 'Das 06:01 �s 12:00'
		WHEN CONVERT(VARCHAR(5), [DataHora], 108) BETWEEN '12:01' AND '18:00' THEN 'Das 12:01 �s 18:00'
		WHEN CONVERT(VARCHAR(5), [DataHora], 108) BETWEEN '18:01' AND '23:59' THEN 'Das 18:01 �s 23:59'
	ELSE
		'INCERTO'
	END PERIODO
FROM
	[GEOEST].[dbo].[VW_GeoRacOcr]

......................

MAIS UMA VEZ..............................MUITO OBRIGADO

Responder

Gostei + 0