SQLTeam.com | Weblogs | Forums

Date time query SQL SERVER

sql-server-2014

#1

I have Date Column Order_Date In varchar Format which stores value as ('14/05/2018 10:26:15'
This is format) ,
Now I Salesman Creating order dailybasis and getting into Order_date column , for a particluar day i need all those order that was crated
before 14:00 P:M

and Second Column I want to extract order after 15:00 P:M i.e Time from 15:00 P:M till 23:00 P:M in SQL SERVER,

My Order_Date is in Var-char data type


#2

That's very tricky with a varchar column. I guess this would do it, but be sure to test it thoroughly:

WHERE Order_Date >= '14/05/2018 00:00:00' AND Order_Date < 14/05/2018 14:00:00'

Similarly for the other datetime.


#3

I want it to change it to Date time Data type, but i am unable to change it.

But Now i have already stored it as varchar and it has 2 Millions record in this format dd/mm/yyyy hh:mm:ss

Now it is not converting into datetime format,,how should i convert it...


#4

This should convert your varchar to datetime:

select CONVERT(datetime, '14/5/2018 10:26:15', 103)


#5
create table #providesampledata(orderid int, order_date varchar(100), 
order_name varchar(50))
--this part is imitating your varchar data type order_date
insert into #providesampledata
select 1 , convert(varchar(20), getdate(), 103) + ' ' + 
convert(varchar(20), dateadd(hh,1,getdate()), 114) , 'chicken wings' union
select 2 ,  convert(varchar(20), getdate(), 103) + ' ' + 
convert(varchar(20),dateadd(hh,2,getdate()), 114)  , 'steak' union
select 3 ,  convert(varchar(20), getdate(), 103) + ' ' + 
convert(varchar(20), dateadd(hh,3,getdate()), 114)  , 'eggs' union
select 4 ,  convert(varchar(20), getdate(), 103) + ' ' + 
convert(varchar(20), dateadd(hh,4,getdate()), 114)  , 'bread' union
select 5 , convert(varchar(20), getdate(), 103) + ' ' + 
convert(varchar(20), dateadd(hh,5,getdate()), 114)  , 'milk' union
select 6 ,  convert(varchar(20), getdate(), 103) + ' ' + 
convert(varchar(20), dateadd(hh,6,getdate()), 114)  , 'welding' union
select 6 ,  convert(varchar(20), getdate(), 103) + ' ' + 
convert(varchar(20), dateadd(hh,9,getdate()), 114)  , 'popsicles'  union
select 6 ,  convert(varchar(20), getdate(), 103) + ' ' + 
convert(varchar(20), dateadd(hh,11,getdate()), 114)  , 'salad' 


;with cte
as
(
--below we are converting that varchar order_date to proper datetime data type using
-- CONVERT with 103 as stated above by denis_the_thief
--https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
select CONVERT(datetime, order_date, 103) as order_date, orderid, order_name
  From #providesampledata
)
select * 
  From cte
  where order_date > '2018-05-15' and order_date <= '2018-05-15 14:00:00'

drop table #providesampledata

#6

I'm thinking to also convert the order_date in the where clause as well.


#7

already being converted in cte?


#8

Oh yeah, sorry.


#9

no worries !:robot: