Saturday, April 25, 2009

Some Useful T-SQL Tricks

Data Formatting dates


This part will return a 4 digit year. We are using format values greater than 100

1.  DECLARE @d DATETIME

2.      SELECT @d = GETDATE()

3.   

4.      SELECT @d AS OriginalDate,

5.      CONVERT(VARCHAR,@d,100) AS ConvertedDate,

6.      100 AS FormatValue,

7.      'mon dd yyyy hh:miAM (or PM)' AS OutputFormat

8.      UNION all

9.      SELECT @d,CONVERT(VARCHAR,@d,101),101,'mm/dd/yyyy'

10.     UNION all

11.     SELECT @d,CONVERT(VARCHAR,@d,102),102,'yyyy.mm.dd'

12.     UNION all

13.     SELECT @d,CONVERT(VARCHAR,@d,103),103,'dd/mm/yyyy'

14.     UNION all

15.     SELECT @d,CONVERT(VARCHAR,@d,104),104,'dd.mm.yyyy'

16.     UNION all

17.     SELECT @d,CONVERT(VARCHAR,@d,105),105,'dd-mm-yyyy'

18.     UNION all

19.     SELECT @d,CONVERT(VARCHAR,@d,106),106,'dd mon yyyy'

20.     UNION all

21.     SELECT @d,CONVERT(VARCHAR,@d,107),107,'Mon dd, yyyy'

22.     UNION all

23.     SELECT @d,CONVERT(VARCHAR,@d,108),108,'hh:mm:ss'

24.     UNION all

25.     SELECT @d,CONVERT(VARCHAR,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'

26.     UNION all

27.     SELECT @d,CONVERT(VARCHAR,@d,110),110,'mm-dd-yyyy'

28.     UNION all

29.     SELECT @d,CONVERT(VARCHAR,@d,111),111,'yyyy/mm/dd'

30.     UNION all

31.     SELECT @d,CONVERT(VARCHAR,@d,112),112,'yyyymmdd'

32.     UNION all

33.     SELECT @d,CONVERT(VARCHAR,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'

34.     UNION all

35.     SELECT @d,CONVERT(VARCHAR,@d,114),114,'hh:mi:ss:mmm(24h)'

36.     UNION all

37.     SELECT @d,CONVERT(VARCHAR,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'

38.     UNION all

39.     SELECT @d,CONVERT(VARCHAR,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'

40.     UNION all

41.     SELECT @d,CONVERT(VARCHAR,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'

 

This part will return mostly a 2 digit year (except for 9, 20 and 21). We are using format values less than 100

1.  DECLARE @d DATETIME

2.      SELECT @d = GETDATE()

3.   

4.      SELECT @d AS OriginalDate,

5.      CONVERT(VARCHAR,@d,0) AS ConvertedDate,

6.      0 AS FormatValue,

7.      'mon dd yyyy hh:miAM (or PM)' AS OutputFormat

8.      UNION all

9.      SELECT @d,CONVERT(VARCHAR,@d,1),1,'mm/dd/yy'

10.     UNION all

11.     SELECT @d,CONVERT(VARCHAR,@d,2),2,'yy.mm.dd'

12.     UNION all

13.     SELECT @d,CONVERT(VARCHAR,@d,3),3,'dd/mm/yy'

14.     UNION all

15.     SELECT @d,CONVERT(VARCHAR,@d,4),4,'dd.mm.yy'

16.     UNION all

17.     SELECT @d,CONVERT(VARCHAR,@d,5),5,'dd-mm-yy'

18.     UNION all

19.     SELECT @d,CONVERT(VARCHAR,@d,6),6,'dd mon yy'

20.     UNION all

21.     SELECT @d,CONVERT(VARCHAR,@d,7),7,'Mon dd, yy'

22.     UNION all

23.     SELECT @d,CONVERT(VARCHAR,@d,8),8,'hh:mm:ss'

24.     UNION all

25.     SELECT @d,CONVERT(VARCHAR,@d,9),9,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'

26.     UNION all

27.     SELECT @d,CONVERT(VARCHAR,@d,11),11,'mm-dd-yy'

28.     UNION all

29.     SELECT @d,CONVERT(VARCHAR,@d,11),11,'yy/mm/dd'

30.     UNION all

31.     SELECT @d,CONVERT(VARCHAR,@d,12),12,'yymmdd'

32.     UNION all

33.     SELECT @d,CONVERT(VARCHAR,@d,13),13,'dd mon yyyy hh:mm:ss:mmm(24h)'

34.     UNION all

35.     SELECT @d,CONVERT(VARCHAR,@d,14),14,'hh:mi:ss:mmm(24h)'

36.     UNION all

37.     SELECT @d,CONVERT(VARCHAR,@d,20),20,'yyyy-mm-dd hh:mi:ss(24h)'

38.     UNION all

39.     SELECT @d,CONVERT(VARCHAR,@d,21),21,'yyyy-mm-dd hh:mi:ss.mmm(24h)'

 How to pad positive and negative number with zeroes in SQL Server?

You have a table with integer values and you are required to always show 8 numbers, if the length of the number is less than 8 characters then you need to pad it. Of course stuff like this should be done at the presentation layer but we all know that sometimes that means reinstalling apps so SQL is the easiest way. Numbers like these are usually order or customer numbers.
The easiest way to pad a number in SQL is by using the following syntax

 

1.   SELECT RIGHT('00000000' + 12345,8)

However running that will still not pad the number with zeroes. You need to convert the number to a varchar first. Run the query below

1.   SELECT RIGHT('00000000' + CONVERT(VARCHAR(8),12345),8)

That returns the output that we want

00012345

Let's continue by creating a table and dumping some numbers in that table

1.   CREATE TABLE #Numbers(Num INT)

2.       INSERT #Numbers VALUES('1')

3.       INSERT #Numbers VALUES('12')

4.       INSERT #Numbers VALUES('123')

5.       INSERT #Numbers VALUES('1234')

6.       INSERT #Numbers VALUES('12345')

7.       INSERT #Numbers VALUES('123456')

8.       INSERT #Numbers VALUES('1234567')

9.       INSERT #Numbers VALUES('12345678')

10.      INSERT #Numbers VALUES('123456789')

Now run the following query

1.   SELECT RIGHT('00000000' + CONVERT(VARCHAR(8),Num),8)

2.       FROM #Numbers

(output)
00000001
00000012
00000123
00001234
00012345
00123456
01234567
12345678
0000000*

As you can see the last row has the value 0000000*. This is because converting to varchar(8) truncated the value. If we increase our convert and right functions to use 9 instead of 8 characters we are fine. Run the same query again.

1.   SELECT RIGHT('00000000' + CONVERT(VARCHAR(9),Num),9)

2.       FROM #Numbers

(output)
000000001
000000012
000000123
000001234
000012345
000123456
001234567
012345678
123456789

As you can see it all looks fine now

What about negative values? What if you want to show -00000123 instead of -123?
First insert these 4 rows

1.   INSERT #Numbers VALUES('-122')

2.    INSERT #Numbers VALUES('-1')

3.    INSERT #Numbers VALUES('-777777')

4.    INSERT #Numbers VALUES('-123456789')

Now we will run the same query again

1.   SELECT RIGHT('00000000' + CONVERT(VARCHAR(9),Num),9)

2.       FROM #Numbers

Here is what those 4 rows look like that we just inserted

(output)
00000-122
0000000-1
00-777777
00000000*

That is not good. Here is what we will do, if the number is negative we will start with a minus sign otherwise we will use a blank and then we will concatenate and replace the minus sign with a blank. This is what it looks like in SQL

1.   SELECT CASE  WHEN Num < 0

2.   THEN '-' ELSE '' END + RIGHT('000000000' + REPLACE(Num,'-',''), 9)

3.    FROM #Numbers

And here is the output

(output)
000000001
000000012
000000123
000001234
000012345
000123456
001234567
012345678
123456789
-000000122
-000000001
-000777777
-123456789

As you can see it is not that difficult to do stuff like this.

Find all tables that contain a certain column

Find all tables that contain a certain column For example return all tables that have the column OrderID in the Northwind database You can get all that information from the INFORMATION_SCHEMA.COLUMNS system view, however that view returns tables as well as views You have to join with INFORMATION_SCHEMA.TABLES and that view contains a column named TABLE_TYPE that you can use to filter on the type (BASE TABLE or VIEW) I have only selected a couple of columns from the views, use * to see them all

1.  USE Northwind

2.  GO

3.   

4.  SELECT c.TABLE_NAME,

5.  TABLE_TYPE,

6.  COLUMN_NAME,

7.  ORDINAL_POSITION,

8.  IS_NULLABLE,

9.  DATA_TYPE,

10. NUMERIC_PRECISION

11. FROM INFORMATION_SCHEMA.COLUMNS c

12. JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME

13. WHERE COLUMN_NAME ='orderid'

14. ORDER BY TABLE_TYPE ,c.TABLE_NAME

How do I format money/decimal data with commas?

Sometimes you want to have your money fields properly formatted with commas like this: 13,243,543.57 You can use the CONVERT function and give a value between 0 and 2 to the style and the format will be displayed based on that.

Below is an example

1.   DECLARE @v MONEY

2.       SELECT @v = 1322323.6666

3.    

4.       SELECT CONVERT(VARCHAR,@v,0)  --1322323.67    

5.       --Rounded but no formatting

6.    

7.       SELECT CONVERT(VARCHAR,@v,1)    --1,322,323.67    

8.       --Formatted with commas

9.    

10.      SELECT CONVERT(VARCHAR,@v,2)    --1322323.6666

11.      --No formatting

 If you have a decimal field it doesn't work with the convert function The work around is to convert it to money first.

1.   DECLARE @v2 DECIMAL (36,10)

2.       SELECT @v2 = 13243543.56565656

3.    

4.       SELECT CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57

5.       --Formatted with commas

1 comments:

Anonymous said...

Please link back to the original article:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-pad-positive-and-negative-number-