Lots of questions come up in the SQL Team forums about conversions between Access and T-SQL and some of the differences between the two SQL dialects. Here's a few handy things to help you out with converting your projects. Check in now and then as this short list will eventually grow as more things come up.
Converting NULL values
Access: NZ(Value, ValueToReturnIfNull)
T-SQL: COALESCE(Value, ValueToReturnIfNull) -- or -- ISNULL(Value, ValueToReturnIfNull)
Checking for NULLs
Access: WHERE Value IS NULL -- or -- WHERE ISNULL(Value)
(note the difference from T-SQL's ISNULL)
T-SQL: WHERE Value IS NULL
String Segments
Access: MID(StringVal, StartPos, [length] )
(length is optional)
T-SQL: SUBSTRING(StringVal, StartPos, length )
(length is required!)
Finding a String within a String
Access
: SELECT INSTR(start, StringToSearch, StringToFind)
T-SQL: SELECT CHARINDEX(start, StringToSearch, StringToFind)
Reverse a String
Access: SELECT STRREVERSE(StringVal)
T-SQL: SELECT REVERSE(StringVal)
Convert a String to Uppercase or Lowercase
Access: SELECT UCASE(StringVal), LCASE(StringVal)
T-SQL: SELECT UPPER(StringVal), LOWER(StringVal)
Formatting Dates, Booleans, Numerics as Strings
Access: SELECT Format(Value, FormatSpecification)
(note: this always returns a string value)
T-SQL:
Do not do this in T-SQL; format data at your front-end application or report
String Literals
Access: SELECT "This is a string"
T-SQL: SELECT 'This is a string'
LIKE pattern matching
matching multiple characters:
Access: WHERE Column LIKE "*string*"
T-SQL: WHERE Column LIKE '%string%'
matching a single character:
Access: WHERE Column LIKE "?string?"
T-SQL: WHERE Column LIKE '_string_'
not matching a character or range:
Access: WHERE Column LIKE "[!a-z]"
T-SQL: WHERE Column LIKE '[^a-z]'
Triming White Space
Access: TRIM(val)
T-SQL: RTRIM(LTRIM(val))
Converting DataTypes
Access: CINT(value), CDBL(value), CDEC(value), CSTR(value), CDATE(value), CBOOL(value)
T-SQL: CONVERT(DATATYPE, value) -- or -- CAST(value AS datatype)
Conditional Expressions
Access: IIF(Condition, ReturnIfTrue, ReturnIfValue)
T-SQL: CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END
Working with Date Literals
Access: WHERE SomeDate = #1/1/2005#
T-SQL: WHERE SomeDate = '1/1/2005'
(this is an implicit conversion from a string to a date)
Creating new Dates
Access: DATESERIAL(year,month,date)
T-SQL:
Use the Date() function here -- there is no quick easy way to do this in T-SQL
Creating new Times
Access: TIMESERIAL(Hour, minute, second)
T-SQL:
Use the Time() function here -- there is no quick easy way to do this in T-SQL
Getting Today's Date and Time
Access: SELECT now()
T-SQL: SELECT getdate()
Getting Today's Date only (i.e., at midnight)
Access: SELECT date()
T-SQL:
Use the DateOnly() function here : SELECT dbo.DateOnly(getdate())
Getting Today's Time Only (at the "base" date, or date with a numeric value of 0)
Access: SELECT Time() (
this returns the time at 12/30/1899)
T-SQL:
Use the TimeOnly() function here : SELECT dbo.TimeOnly(getdate()) (
returns the time at 1/1/1900)
Boolean (True/False) Values
Access: WHERE Active = True -- and -- WHERE Active = False
(Active is a Boolean datatype)
T-SQL: WHERE Active=1 -- and -- WHERE Active=0
(Active is a Bit datatype)
Returning or Setting Boolean Values
Access: SELECT BooleanExpression
T-SQL: CAST(CASE WHEN BooleanExpression THEN 1 ELSE 0 END) AS BIT
FULL OUTER JOINS
(Note: try to avoid these as a general practice)
Access: SELECT ... FROM tableA LEFT OUTER JOIN tableB ON ...
UNION ALL
SELECT ... FROM tableB LEFT OUTER JOIN tableA ON ... WHERE tableA .PK IS NULL
T-SQL: SELECT ... FROM tableA FULL OUTER JOIN tableB ON ....
RIGHT OUTER JOINS
Because we all know that using the query designer in Access sometimes results in these, but we should never use them in manually written and maintained SQL:
Access: SELECT ... FROM tableA
RIGHT OUTER JOIN tableB ON ....
T-SQL: SELECT ... FROM tableB
LEFT OUTER JOIN tableA ON ....
Parameters
Access:
SELECT [Any column name not defined]
T-SQL: SELECT @ParamName
Modulo Operator
Access: SELECT value1 MOD value2
T-SQL: SELECT value1 % value2
Dividing Integers to calculate a Percentage or other result with decimal places
Access: SELECT Int1 / Int2
(this returns a Double value implicitly)
T-SQL: SELECT Int1 * 1.0 / Int2 (
the multiplication by 1.0 results in a numeric(8,6) being returned)
String Concatenation Operator
Access: Val1 & Val2
(both will be implicitly converted to strings if they are not already)
T-SQL: Val1 + Val2
( note that explicit conversion to a "string" datatypes is necessary in T-SQL)
Referencing an Expression in a SELECT
Here, we define A+B as a new column X, and we want to reference X in the SELECT:
Access: SELECT
A+B as X, X+C as D FROM ...
T-SQL: SELECT
X,
X+C as D FROM (SELECT
A+B as X, C FROM ... ) tmp
Getting a Character from an ASCII code
Access: SELECT CHR(AsciiCode)
T-SQL: SELECT CHAR(AsciiCode)
Getting an ASCII code from a Character
Access: SELECT ASC(Character)
T-SQL: SELECT ASCII(Character)
Date Part Indicators (DateAdd, DateDiff, DatePart)
MS Access and SQL Server both use the same basic date functions (DateAdd, DateDiff, DatePart) but the way you indicate which "date part" you are after differs between the two.
MS Access uses a
string expression to indicate the "dart part" in DateAdd, DatePart and DateDiff expressions; SQL Server uses
symbols. Thus, you need to put quotes around the part name in MS Access since it is just a string expression, but you should NOT use quotes in SQL Server -- just enter the value directly.
The Date Part indicators are listed below:
| Date Part |
SQL Server |
MS Access |
| Year |
year, yy, yyyy |
"yyyy" |
| Quarter |
quarter, qq, q |
"q" |
| Month |
month, mm, m |
"m" |
| Day of Year |
dayofyear, dy, y |
"y" |
| Day |
day, dd, d |
"d" |
| Week |
week, wk, ww |
"ww" |
| Day of Week |
weekday, dw |
"w" |
| Hour |
hour, hh |
"h" |
| Minute |
minute, mi, n |
"n" |
| Second |
second, ss, s |
"s" |
| Millisecond |
millisecond, ms |
- |
Finally, note that both Access and T-SQL support the Year(), Month() and Day() functions.