Web Resources Database

TSQL Date Time Formatting

Using the CONVERT function, with different styles, allows you to display date and time data in many different formats. Let's look at what I am taking about. The easiest way to demonstrate how to use the CONVERT function is to review some TSQL code that displays the current time in a few different display formats. The following script uses only the CONVERT function to display the different formats.

PRINT '1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>'   
CONVERT(CHAR(19),GETDATE())  
PRINT '2) HERE IS MM-DD-YY FORMAT ==>'   
CONVERT(CHAR(8),GETDATE(),10)  
PRINT '3) HERE IS MM-DD-YYYY FORMAT ==>'   
CONVERT(CHAR(10),GETDATE(),110) 
PRINT '4) HERE IS DD MON YYYY FORMAT ==>'   
CONVERT(CHAR(11),GETDATE(),106)
PRINT '5) HERE IS DD MON YY FORMAT ==>'   
CONVERT(CHAR(9),GETDATE(),6) 
PRINT '6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>'   
CONVERT(CHAR(24),GETDATE(),113)

Here is the output from the above script:

1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>Feb  5 2003  5:54AM
2) HERE IS MM-DD-YY FORMAT ==>02-05-03
3) HERE IS MM-DD-YYYY FORMAT ==>02-05-2003
4) HERE IS DD MON YYYY FORMAT ==>05 Feb 2003
5) HERE IS DD MON YY FORMAT ==>05 Feb 03
6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>05 Feb 2003 05:54:39:567

As you can see, this script displays the current date in many different formats. Some formats have two digit years, while others have four digit years. Some displays have 24 hour or AM/PM time formats. Still others have the month displayed as a numeric value, while others have a month abbreviation. Some of the displays also have the date displayed in DD MON YYYY format. I suggest you review Books Online for a description of all the formats that the CONVERT function can display using different styles.

Even though the CONVERT function has a number of data/time output styles, it still might not have the exact display format you need to display. Sometimes you will need to use other TSQL functions as well, to get the display format you desire.

Database Resources & Links

Remove Carriage Return, Line Feed, Tab in TSQL

-- remove carriage return, line feed, and tab from a field in tsql
REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

Great SQL Help.  First place I go to to find help with Complex Unions or functions. Tech on the Net

Q. how do i tune my sql queries?

A. Performance Tips for Queries

Carefully monitor the indexes on the table in your queries to make sure your queries are using them.

Try not to use WHERE clauses that don’t use SARG logic. For example, OR, <<>>, !=, !<<, !>>, IS NULL, NOT, NOT EXISTS, NOT IN, NOT LIKE, and LIKE clauses cannot use the SARG logic, which slows down the Query Optimizer drastically. You may find that some of your queries that use these clauses are not using indexes.

Avoid using cursors whenever possible. Before moving forward with the cursor, seeif you can do the same operation with a normal query. Consider using a temp table instead of the cursor, since there is less overhead involved.

Avoid using UNION statements, unless you’re removing duplicate rows. Instead, use a UNION ALL statement, which is much faster and doesn’t look for duplicate rows.

Always list your column names when performing a select statement. If a column is added in the schema, it could harm your application if not properly handled, since you’re pulling down a larger resultset than the application is expecting. Your network time could be much reduced if you only return data that you need.

Consider breaking large tables into smaller views. A good view could take a subset of the records based on a date or location.

If you don’t have a requirement to remove duplicates or order the data, avoid using an ORDER BY or DISTINCT statement. If there is no clustered index on the column to satisfy the query, a temporary workspace must be created to fulfill the query, which can take quite a long time for large tables.

Use the EXISTS clause rather than the IN clause. The EXISTS clause is slightly faster.

Use the TOP statement if you need a limited amount of records.

Non-logged SQL Statements
The following tips could help speed up delete and insert processes in your applications:

The TRUNCATE TABLE <> command is much faster than the DELETE command, since the former command is non-logged. Non-logged statements only write minimal data into the transaction log. Because it is non-logged, there is an element of danger since it is not as easily recovered if you make a mistake. This command can only be run on tables that don’t have foreign keys. The TRUNCATE TABLE command does not support any type of WHERE clause.

Use commands that minimize logging, such as BULK INSERT, TRUNCATE TABLE, and SELECT…INTO. For text, ntext, and image fields, use WRITETEXT and UPDATETEXT commands, which lower the amount of logging.

Add table locks on inserts where you can. For example, if you’re performing a data load at 2 A.M. and you’re not worried about anyone reading from the table, you can easily use a table lock. You can do this with the TABLOCK hint when you issue a BULK INSERT command.