Error in date conversion [Resolved]

Posted by Klbaiju under Sql Server on 3/19/2014 | Points: 10 | Views : 1157 | Status : [Member] | Replies : 3
Hi,

I want to check a date in sqlserver

my table contains following data

EmpId EmpName InTime
2500 Sachin 2014-01-01 09:00:00.000
2500 Sachin 2014-01-01 11:00:00.000

i want to check with following query

select * from baiju.dbo.HbaEmp where CONVERT(CHAR(10), InTime, 109) = '01-jan-2014'

but it doesn't return any value. i want to check with above format only

how it is possible

Regards

Baiju




Responses

Posted by: Bandi on: 3/21/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

--Sample Data
declare @tab table (EmpId int, EmpName varchar(100), InTime datetime)
insert @tab
SELECT 2500, 'Sachin', '2014-01-01 09:00:00.000 ' union all
SELECT 2500, 'Sachin', '2014-01-01 11:00:00.000 '

--Query
select * from @tab where REPLACE(CONVERT(VARCHAR(11), InTime, 106), ' ', '-') = '01-jan-2014'



NOTE: put your table instead of @tab in Query

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 3/19/2014 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,
You need to change your sql query slightly. The problem here is the format which you are using is wrong. You need to use 106 instead of 109 and also you need to allow 11 characters to get all the values .
Change your query like given below

select * from baiju.dbo.HbaEmp where CONVERT(CHAR(11), InTime, 106) = '01-jan-2014'



Thanks,
A2H
My Blog

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 3/21/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
CONVERT(CHAR(11), InTime, 106) will give 'DD MON YYYY'

But search date is in DD-MON-YYYY

So replace spaces with '-' symbol..........

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response