How to return day of the week and time formatted sql server 2008?

Question

I need to make a query as follows, I have a table (script):

1 - Take the day of the week

2 - take server time in format hh:mm

example:

SELECT 
 CASE DATEPART(DW, GETDATE()) 
     WHEN 1 THEN 'DOMINGO'
     WHEN 2 THEN 'SEGUNDA'
     WHEN 3 THEN 'TERCA'
     WHEN 4 THEN 'QUARTA'
     WHEN 5 THEN 'QUINTA' 
     WHEN 6 THEN 'SEXTA'
     WHEN 7 THEN 'SABADO'
  END DIASEMANA,
  CONVERT(VARCHAR(05),GETDATE(),108) AS 'HORA',
* FROM TB_ESTRACAO
where DIASEMANA = 'S'

Mount a query this way:

select * from TB_ESTRACAO where HORA_FECHAMENTO > 'HORA' and 'DIASEMANA' = 'S'

USE [TESTE]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_ESTRACAO](
    [IDEXTRACAO] [int] IDENTITY(1,1) NOT NULL,
    [DESCRICAO] [varchar](100) NULL,
    [HORA_FECHAMENTO] [char](10) NULL,
    [PREMIACAO_MAXIMA] [int] NULL,
    [SEGUNDA] [char](1) NULL,
    [TERCA] [char](1) NULL,
    [QUARTA] [char](1) NULL,
    [QUINTA] [char](1) NULL,
    [SEXTA] [char](1) NULL,
    [SABADO] [char](1) NULL,
    [DOMINGO] [char](1) NULL,
    [STATUS] [char](1) NULL,
    [MARCA] [char](1) NULL,
    [DATA_INC] [datetime] NULL,
    [DATA_HAB] [datetime] NULL,
    [DATA_ALT] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [IDEXTRACAO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TB_ESTRACAO] ADD  DEFAULT ((0)) FOR [PREMIACAO_MAXIMA]
GO
ALTER TABLE [dbo].[TB_ESTRACAO] ADD  DEFAULT ('N') FOR [SEGUNDA]
GO
ALTER TABLE [dbo].[TB_ESTRACAO] ADD  DEFAULT ('N') FOR [TERCA]
GO
ALTER TABLE [dbo].[TB_ESTRACAO] ADD  DEFAULT ('N') FOR [QUARTA]
GO
ALTER TABLE [dbo].[TB_ESTRACAO] ADD  DEFAULT ('N') FOR [QUINTA]
GO
ALTER TABLE [dbo].[TB_ESTRACAO] ADD  DEFAULT ('N') FOR [SEXTA]
GO
ALTER TABLE [dbo].[TB_ESTRACAO] ADD  DEFAULT ('N') FOR [SABADO]
GO
ALTER TABLE [dbo].[TB_ESTRACAO] ADD  DEFAULT ('N') FOR [DOMINGO]
GO
ALTER TABLE [dbo].[TB_ESTRACAO] ADD  DEFAULT ('N') FOR [STATUS]
GO
ALTER TABLE [dbo].[TB_ESTRACAO] ADD  DEFAULT ('N') FOR [MARCA]
GO

Answer 1

The solution I found was to make two queries separately, leaving the result so:

using System;
using System.Collections.Generic;
using System.Linq;
using Generico.Repositorio;
using Generico.Dominio;
using System.Data.SqlClient;

namespace Generico.Aplicacao
{
    public  class EstracaoAplicacao
    {
        private Contexto contexto;

        string Hora;
        string DiaSemana;

        public List<TB_ESTRACAO> ListarTodos()
        {
            //faz a consulta para pegar o dia e hora
            ListarDiaHoraSemana();
            //aqui estou usando o dia é hora para filtra os registros
            var strQuery = string.Format("select * from TB_ESTRACAO where HORA_FECHAMENTO >  '"+Hora+
                                         "' and " + DiaSemana +  " = 'S' "); 

            using (contexto = new Contexto())
            {
                var retornoDataReader = contexto.ExecutaComandoComRetorno(strQuery);
                return TransformaReaderEmListaObjetos(retornoDataReader);
            }

        }



        public List<TB_ESTRACAO> ListarDiaHoraSemana()
        {
            var strQuery = string.Format(
                                        " SELECT top(1)" +
                                        " CASE DATEPART(DW, GETDATE())" +
                                        " WHEN 1 THEN 'DOMINGO'" +
                                        " WHEN 2 THEN 'SEGUNDA'" +
                                        " WHEN 3 THEN 'TERCA'" +
                                        " WHEN 4 THEN 'QUARTA'" +
                                        " WHEN 5 THEN 'QUINTA'" +
                                        " WHEN 6 THEN 'SEXTA'" +
                                        " WHEN 7 THEN 'SABADO'" +
                                        " END DIASEMANA," +
                                        " CONVERT(VARCHAR(05), GETDATE(), 108) AS 'HORA'," +
                                        " * FROM TB_ESTRACAO"
                                     );

            using (contexto = new Contexto())
            {
                var retornoDataReader = contexto.ExecutaComandoComRetorno(strQuery);
                return TransformaReaderEmListaObjetosEspecifico(retornoDataReader);
            }

        }


        public TB_ESTRACAO ListarPoId(int id)
        {
            var strQuery = string.Format("select * from TB_ESTRACAO where IDEXTRACAO = {0}", id);

            using (contexto = new Contexto())
            {
                var retornoDataReader = contexto.ExecutaComandoComRetorno(strQuery);
                return TransformaReaderEmListaObjetos(retornoDataReader).FirstOrDefault();
            }
        }

        private List<TB_ESTRACAO> TransformaReaderEmListaObjetos(SqlDataReader reader)
        {
            var retornando = new List<TB_ESTRACAO>();
            while (reader.Read())
            {

                TB_ESTRACAO tabela = new TB_ESTRACAO()
                {
                    idextracao = reader["IDEXTRACAO"] == DBNull.Value ? 0 : Convert.ToInt32(reader["IDEXTRACAO"]),
                    descricao = reader["DESCRICAO"] == DBNull.Value ? string.Empty : reader["DESCRICAO"].ToString(),
                    hora_fechamento = reader["HORA_FECHAMENTO"] == DBNull.Value ? string.Empty : reader["HORA_FECHAMENTO"].ToString(),
                    segunda = reader["SEGUNDA"] == DBNull.Value ? string.Empty : reader["SEGUNDA"].ToString(),
                    terca = reader["TERCA"] == DBNull.Value ? string.Empty : reader["TERCA"].ToString(),
                    quarta = reader["QUARTA"] == DBNull.Value ? string.Empty : reader["QUARTA"].ToString(),
                    quinta = reader["QUINTA"] == DBNull.Value ? string.Empty : reader["QUINTA"].ToString(),
                    sexta = reader["SEXTA"] == DBNull.Value ? string.Empty : reader["SEXTA"].ToString(),
                    sabado = reader["SABADO"] == DBNull.Value ? string.Empty : reader["SABADO"].ToString(),
                    domingo = reader["DOMINGO"] == DBNull.Value ? string.Empty : reader["DOMINGO"].ToString()
                };

                retornando.Add(tabela);
            }
            reader.Close();
            return retornando;
        }


        private List<TB_ESTRACAO> TransformaReaderEmListaObjetosEspecifico(SqlDataReader reader)
        {
            var retornando = new List<TB_ESTRACAO>();
            while (reader.Read())
            {
                DiaSemana = reader["DIASEMANA"] == DBNull.Value ? string.Empty : reader["DIASEMANA"].ToString();
                Hora = reader["HORA"] == DBNull.Value ? string.Empty : reader["HORA"].ToString();
            }
            reader.Close();
            return retornando;
        }

    }
}