[ Zidar @ 18.07.2012. 21:41 ] @
Na ovom sajtu je dato lepo poredjenje SQL naredbi koje se koriste u Accessu i MS SQL serveru: http://weblogs.sqlteam.com/jef...T-SQL-to-T-SQL-Cheatsheet.aspx Za slucaj da se navedeni sajt ugasi, evo: Code: 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. |