How to place single quotes to date in like statement

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi

query like:

SQL

where CONVERT(VARCHAR(10),CreatedDateTime,111) LIKE ''' +2012-07-25 + '%''

Please check my where condition, did I place the single quotes correctly where 2012-07-25 it is in nvarchar type.Where CreatedDateTime is like 2012-07-25 13:51:59.537.

I am getting an error is:

The data types varchar and varchar are incompatible in the modulo operator.

Why I written like this I want to check on a particular date from user on CreatedDateTime contains 2012-07-25.
I think is little bit confusing to you but try to understand my problem and please place the single quotes in correct.

Thanking you.

Posted 8-Aug-12 19:57pm

Madhugundi

Updated 8-Aug-12 20:33pm

v3

Add a Solution

2 solutions

Solution 2

SQL

where CONVERT(VARCHAR(10),CreatedDateTime,101) LIKE '07/25/2012%'

Thanks
Ashish

Posted 8-Aug-12 20:44pm

AshishChaudha

Comments

Madhugundi 9-Aug-12 3:02am

i tried like ur post but i am getting Zero count for every hour

AshishChaudha 9-Aug-12 3:49am

Try this once..I had tried using my tables..working fine

SELECT DATEPART(HOUR, convert(Datetime,CreatedDateTime,13)) AS [Hour of Day] , Id AS [TotalShipOrders] FROM ShipmentDetail where CONVERT(VARCHAR,CreatedDateTime,101) LIKE '07/25/2012%' Union ALL SELECT Hour, NULL From @Hours

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-08T20:19:00

Solution 1

try this

SQL

where CONVERT(VARCHAR(10),validfromdate,111) LIKE '2012/07/25%'

Posted 8-Aug-12 20:19pm

Santhosh Kumar Jayaraman

Comments

Madhugundi 9-Aug-12 3:02am

i tried like ur post but i am getting Zero count for every hour

Santhosh Kumar Jayaraman 9-Aug-12 3:03am

for every hour? i didnt understand that, can you explain me?You are searching with hour also?

Madhugundi 9-Aug-12 3:13am

Declare @Hours Table (Hour int);
Declare @i as int;
Set @i = 5
While @i < 24
Begin
Insert into @Hours
Values(@i);

Set @i = @i + 1;
End;

Select
[Hour of Day],
Count([TotalShipOrders]) as Total
From
(
SELECT
DATEPART(HOUR, convert(Datetime,CreatedDateTime,13)) AS [Hour of Day]
, Id AS [TotalShipOrders]
FROM ShipmentDetail where CONVERT(VARCHAR(10),CreatedDateTime,111) LIKE +2012-07-25 + '%'
Union ALL
SELECT Hour, NULL
From @Hours
) as Data

GROUP BY [Hour of Day]
ORDER BY [Hour of Day]

i want the where condition is like
where CONVERT(VARCHAR(10),CreatedDateTime,111) LIKE '2012-07-25 %'

2012-07-25 it is passed by user from Application.
CreatedDateTime is like 2012-07-25 15:54:32.843

Plese help how to place single quotes.

Madhugundi 9-Aug-12 3:14am

Based on 2012-07-25 this date i want get records from CreatedDateTime is like 2012-07-25 15:54:32.843 i am grouping by hour

Santhosh Kumar Jayaraman 9-Aug-12 3:15am

you have to use '/' in date instead of 2012-07-05.. It should be 2012/07/05.. Use replace function to replace - with /

Madhugundi 9-Aug-12 3:37am

Thank You.

Santhosh Kumar Jayaraman 9-Aug-12 3:37am

is that working?

Madhugundi 9-Aug-12 4:05am

ya its working but instead of static date i want pass date as a variable like
Declare @CurrentDate varchar;
Set @CurrentDate=(select REPLACE('2012-07-26','-','/'));
in where condition how to concatinate % to C.
I am trying in where clause like '@CurrentDate%' i know it will be wrong.
so please help me i am new for sqlserver.

Santhosh Kumar Jayaraman 9-Aug-12 4:55am

No Percentage is not required..
Check this query.
Declare @CurrentDate nvarchar(10)
set @CurrentDate='2012-08-02'
Set @CurrentDate=(select REPLACE(@CurrentDate,'-','/'))

select @CurrentDate
select Replace(CONVERT(VARCHAR(10),validfromdate,111),'/','-') ,*
from classtag_lkp where CONVERT(VARCHAR(10),validfromdate,111) like @CurrentDate

Replace it with your table and column names.
when we use this CONVERT(VARCHAR(10),validfromdate,111)= it will give date in '2012/08/02' format ,no time will be there.
Mark this answer as accepted if it works

Madhugundi 9-Aug-12 5:22am

i tried it but not it retuns zero count for every hour