MSDN TSQL forum | SQL with Manoj

How to retrieve last 12 months records form a table – MSDN TSQL forum

March 24, 2011 Leave a comment

–> Question:

I have a data field in my table that i would to use within my WHERE clause, to get the last 12 months dataset.

So for example, WHERE date between ‘20110323’ and ‘20100323’

Is this possible? I know I have to use something like GETDATE() or something but not quite sure how to incorporate this.

–> My Answer:

You can make your WHERE clause like this:

WHERE date_field between getdate() and getdate()-365

-- OR

WHERE date_field between getdate() and dateadd(m,-12,getdate()

–> Answer by Uri Dimant:

You can make your WHERE clause like this:

SELECT * 
FROM tbl 
WHERE dt >= DATEADD(month,-12,DATEADD(day,DATEDIFF(day,0,GETDATE()),0)) 
AND dt <=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

Ref link.

Categories: SQL Server Questions, SQL Tips Tags: MSDN TSQL forum

Is WITH (READPAST) affected by readlocks – MSDN TSQL forum

March 19, 2011 Leave a comment

–> Question:

If a transaction calls a SELECT using the table hint WITH(READPAST)… will it skip rows that are locked by other SELECT statements that are in READ COMMITTED mode and are issuing shared locks?

ie. Does READPAST skip rows that are locked by SELECT statements, not just UPDATE and INSERT statements?

–> Answer:

I don’t think READPAST will skip rows locked by SELECT statement, but only in UPDATE & DELETEs.

Check here for an example and demo: Difference between NOLOCK and READPAST table hints

You may also check a similar old post in MSDN forum for more into.

Ref link.

Categories: SQL Server Questions Tags: MSDN TSQL forum, Read Lock, READPAST, SHARED Locks

Convert unicode Characters to be displayed in ASCII format – MSDN TSQL forum

March 18, 2011 Leave a comment

–> Question:

Is there a function (or any other way) in T SQL, by which I can somehow convert a unicode character (UTF – 8) and then display it in normal ASCII format?

Eg: I want to convert “Ha Nội” to “Hanoi” either programatically or in SQL.
Another Example: Réunion as “Reunion”

One other problem is sometimes some characters in unicode come up as “?” when cast using the above method:

Eg: Ḩaḑramawt ?a?ramawt

–> Answer:

This is related to database COLLATION settings.

This would not be the best way, but just in case an idea to sail you through….

SELECT 
	'Réunion', 
	CAST('Réunion' AS VARCHAR(100)) COLLATE SQL_Latin1_General_CP1253_CI_AI

… you can also play with COLLATE DATABASE_DEFAULT option.

For more info you can check my blog post on COLLATION, Collation Conflicts and Change a Database Collation.

Regarding your other problem, you want to match the foreign language characters to English, right? Which is not possible in most of the cases.

Just like in French, Réunion, contains ‘é’ which is similar to English ‘e’ but with l’accent aigue accent.

But this is not feasible for every character, and other languages too, like Chinese, Hindi. How can you map, and even if you map what symbol will it show? And thus every character cannot be matched against English and you’ll see invalid characters in your result set.

Ref link.

Categories: SQL Server Questions Tags: COLLATE DATABASE_DEFAULT, Collation Conflict, Database Collation, MSDN TSQL forum

Convert Hex to String – MSDN TSQL forum

December 22, 2010 Leave a comment

–> Question:

How can I convert the hex code ‘0x16004D616E75623232’ to string?

An online utility provides me its equivalent: http://www.string-functions.com/hex-string.aspx

But how can I do this by TSQL, CLR would be a second thought.

–> Answer:

Didn’t thought it was so simple, actually my hex string had ‘1600’ prefixed after 0x, on removing it I’m getting the expected o/p, which is:

SELECT CONVERT(varchar(64), 0x4D616E75623232, 0)

–> Answer from Hunchback:

If you are using SS 2008 or earlier, then check function CONVERT in BOL.

SELECT
  CONVERT(varbinary(64), '0x16004D616E75623232', 1),
  CONVERT(varchar(64), 0x16004D616E75623232, 1),
  CONVERT(varchar(64), 0x16004D616E75623232, 2);
GO

For lower versions you can use some tricks from Umachandar Jayachandran, link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/20e92e10-a0ab-4a53-a766-76f84bfd4e8c

Ref link

Categories: SQL Tips Tags: Hex to String, MSDN TSQL forum