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
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.
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...
This should convert your varchar to datetime:
select CONVERT(datetime, '14/5/2018 10:26:15', 103)
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 #providesampledataI'm thinking to also convert the order_date in the where clause as well.
already being converted in cte?
Oh yeah, sorry.
no worries !