Error in date conversion [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	--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


--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

0	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

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

0	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

Latest Posts