Data conversion in SQL server after using in Where clause?

Asked

Viewed 58 times

-3

I need to extract sales from such a date period until such date. For example, between 01/04/2011 and 30/04/2011.

I am not able to bring the result of the period in question. In this case, I am using the table Adventuresworks2019 from Microsoft to practice.

This is as far as I go:

select pp.productID, 
       pp.Name as "Descrição do produto", 
       pp.ProductNumber as "Número do Produto", 
       po.Unitprice as "Preço Unitário",
       po.LineTotal as "valor total", 
       po.Orderqty as "Quantidade do Produto", 
       po.duedate as "Data do Pedido"
    from production.product pp 
    inner join Purchasing.PurchaseOrderDetail po 
    on pp.ProductID = po.ProductID
    where DueDate ( Daqui não consigo passar)

I need exactly the period between certain dates.

Also, the field is in format datetime, that is, the date appears as 2011-04-30 00:00:00:000, but I want to exhibit how 30/04/2011.

sql sql-server datetime select-sql

  • Should the date "04/30/2011" participate in the result? Is it the full month or really between dates? Is that it has the question of being between inclusive dates or the possibility of excluding the initial and final dates.

  • Good morning. So, I need full date , only month day and year, but if I have to convert before ? ie I have to use in select ? or I have to use in whwere? because I have not yet had the expected result , I because within the specified date still has information and is not searching

  • In your example, you use the start date and the end date representing a full month. Are you always interested in a full month? ex: 05/2020, 06/2020. Or you can have periods between dates, ex: 15/05/2020 to 15/06/2020 ?

  • as the field is in datetime format 2011-04-30 00:00:00:000 I want to show only the date I selected between 01/04/2011 until 30/04/2011 but where I use the conversion ? i use in Where or select? Please I need these tips, because in the date and string functions I am still Noob. Thank you.

2 answers

1


For date formatting, I used convert(varchar(10),po.DueDate ,103), and to the where used where po.DueDate between '01/04/2011' and '30/04/2011'.

The query was like this:

select
  pp.productID,
  pp.Name as "Descrição do produto",
  pp.ProductNumber as "Número do Produto",
  po.Unitprice as "Preço Unitário",
  po.LineTotal as "valor total",
  po.Orderqty as "Quantidade de Produto",
  convert(varchar(10),po.DueDate ,103) as "Data de Pedido"
from production.product pp 
  inner join Purchasing.PurchaseOrderDetail po 
  on pp.ProductID = po.ProductID
where po.DueDate   between  '01/04/2011' and '30/04/2011'
  order by po.DueDate

I did the direct conversion of the column that was in the format datetime and the result was the expected.

inserir a descrição da imagem aqui

0

If you will filter through the duedate field of the Purchaseorderdetail table, and this field is datetime, you can filter by date range using BETWEEN. The filter by the date range you entered would look like this:

select pp.productID, 
       pp.Name as "Descrição do produto", 
       pp.ProductNumber as "Número do Produto", 
       po.Unitprice as "Preço Unitário",
       po.LineTotal as "valor total", 
       po.Orderqty as "Quantidade do Produto", 
       po.duedate as "Data do Pedido"
    from production.product pp 
    inner join Purchasing.PurchaseOrderDetail po 
    on pp.ProductID = po.ProductID
    where po.duedate BETWEEN '2011-04-01 00:00:00' and '2011-04-30 23:59:59'

  • then, if I do this way of formatting error but if I use the date without the bar , '20110401 ' and '20110430' it brings with the date and time, but still within the date not searching, because the date format is not interesting, I want to present this way 01/04/2011.

Browser other questions tagged sql sql-server datetime select-sql

You are not signed in. Login or sign up in order to post.