TSQL-How to Contract Numbers

I was trying to contract numbers from “1” to “1st”, 2 to “2nd” etc.

This amazing solution from Brad Schulz at

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/6d176328-908c-4b00-8768-eb71b7c57e7b

did the job for me.

SELECT CAST(Number AS VARCHAR(10))
      +CASE 
         WHEN Number%10=1 AND Number%100<>11 THEN 'st'
         WHEN Number%10=2 AND Number%100<>12 THEN 'nd'
         WHEN Number%10=3 AND Number%100<>13 THEN 'rd'
         ELSE 'th' 
       END
FROM master..spt_values
WHERE type='P' 
Advertisements

TSQL– From Datetime to String in yyyy-mm-dd hh:mm:ss.mmm Format

I had this requirement to create some dynamic SQL script for a client.  I had a data source with dates in UK format.  The script had to have dates in datetime’s normal format.  I was creating the script from the data source.  Lets look at an example:

 

I had a varchar value ‘18/10/2012’ in the data source.  I had to put equivalent datetime value in the database.  So the dynamic script was required to have correct value.

Using CONVERT (DATETIME, value, 103) gave me the correct datetime format but using that for dynamic SQL was not possible as the returned result was DATETIME.

So I had to again CONVERT the DATETIME into VARCHAR with option 121.  Here is an example:

 

 

2012-10-24_1037

T-Mobile, Orange and Everything Everywhere 3G Problem

Last morning I received an SMS from T-mobile (my current provider) stating that if I restarted my phone set I would see EE instead of T-mobile as a signal provider.  I did the restart and was able to see EE instead of T-Mobile at the top of the screen.  All fine and well.  Problem started when I wanted to make a phone call.  I was getting disconnected right away.  I went to the nearest T-mobile centre (Barnet Spires Mall), and the very helpful lady there was able to solve the issue.  Apparently Everything-Everywhere is having problem with its 3G network because of 4G network work.  The lady set my phone to get the 3G network from a manually selected list (when searching for available list two EE options came, she selected the bottom one).  The phone started working.  As I normally use the mobile only for phone calls I asked her is it better to change signal to 2G (GMS instead of UMTS).  She said that was also worth a try.  I changed my signal type to 2G.  This was also working fine.  During this rollover period for 4G I think this problem will go on for some time.

T-Mobile, Orange and Everything Everywhere 3G Problem

Last morning I received an SMS from T-mobile (my current provider) stating that if I restarted my phone set I would see EE instead of T-mobile as a signal provider.  I did the restart and was able to see EE instead of T-Mobile at the top of the screen.  All fine and well.  Problem started when I wanted to make a phone call.  I was getting disconnected right away.  I went to the nearest T-mobile centre (Barnet Spires Mall), and the very helpful lady there was able to solve the issue.  Apparently Everything-Everywhere is having problem with its 3G network because of 4G network work.  The lady set my phone to get the 3G network from a manually selected list (when searching for available list two EE options came, she selected the bottom one).  The phone started working.  As I normally use the mobile only for phone calls I asked her is it better to change signal to 2G (GMS instead of UMTS).  She said that was also worth a try.  I changed my signal type to 2G.  This was also working fine.  During this rollover period for 4G I think this problem will go on for some time.