Looking for a fast and easy tip to speed up your SQL queries then look no further. Using a conditional AND in your join will improve performance rather than putting it in the traditional WHERE clause. Let me explain by using a simple book order example.
I think SQL does a pretty good job with dates. Here is a very simple way to get the last day of any month. The example retreaves the last day of the last month.
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
Replace the GETDATE() with whatever date you need to find the last day of the month before.
Last week, I was tasked with with sending out an email of hourly production data that was to be read on blackberry phones. It started off as something really simple; just sending a plain html table in an email.
Here is just another little way to remove white space. Use SQL Trim(), lTrim() and rTrim().
SELECT Trim( ' Variable ' );
Output: 'Variable'
SELECT lTrim( ' Variable ' );
Output: 'Variable '
SELECT rTrim( ' Variable ' );
Output: ' Variable'
So I ran into a little problem with some of my SQL between statements this last week. The new year was causing no data to be shown in some data grids and graphs but only if the between statement included data from 2008. Here is my old between statement and after I will show the fix. It is pretty simple but maybe it will be helpful to someone else.
DOES NOT WORK!!!
| WHERE CONVERT(VARCHAR(10), date, 101) BETWEEN CONVERT(VARCHAR(10), #DateField1#, 101) AND CONVERT(VARCHAR(10), #DateField2#, 101) |
NEW BETWEEN STATEMENT THAT WORKS
| WHERE date BETWEEN CONVERT(VARCHAR(10), #DateField1#, 101) AND CONVERT(VARCHAR(10), #DateField2#, 101) |
For some reason the first CONVERT statement did not like having the range between two different years. Hope this saves someone a couple minutes ;)
MySQL has a really helpful LIMIT feature that allows you to return a specific amount of rows from a query.
SQL Server 2005 doesn't have the LIMIT clause, but there are a few other functions that can be used instead.
First there's the SET ROW COUNT which allows you to specify the number of rows to return. The code below will return 7 Records:
"font-size: 10pt;">SET ROWCOUNT 7
SELECT *
FROM tblMyTable
ORDER BY MyDate
Last night, I was a working on a problem of trying to diplay entries from a junction table. The entries in the junction table needed to diplay in one row depending on ID types. I wasn't sure of the best way to accomplish this, but I discovered the function QueryAddColumn(). QueryAddColumn() can be used to add an array as a column in a query object. I am thinking about writing a custom tag to perform this operation in my application, but I haven't got that far yet. My sample script below provides an example of how QueryAddColumn works.
As a new member of the Coldfusion Team at OHL logistics, I would like to say hello to everyone who participates in CFTips.net. I have only been working with CF for about two weeks, and I have run into a few problems here and there while developing my first CF App. One problem in particular, displaying months instead of numbers using a query and cfchart tag, caused a huge headache.
The solution to this problem is simple. Use the SQL function DATENAME(mm,'fieldname') and output the column in your cfchart tag.
Here is the Output:
I was trying to format percentages and could not figure out why the number was rounding up to the nearest integer. I did a <cfquery> and did my percentage calculations within a SELECT statement. I then tried <cfoutput> and <cfdump> still showing only integers. I proceeded to try LSNumberFormat and made sure my variables in T-SQL were set to allow decimals. I still had no luck of removing the rounding problem.
Recent Comments