In a queries like
SELECT dividend/divisor AS data FROM table
The divisor can have a zero value, then SQL server returns an error “Division by zero encountered”.
To avoid this we have to always check the divisor for zero value
END AS data
Starts from version 2008 there are special types to store only date or time. These are Date and Time accordingly. But if a program should support old versions of SQL server then both date and time are stored together in DateTime field. It looks like for example ‘2012-09-02 02:07:38.430’.
Say we need to find all records for one day 2012-09-02.
The simple string comparison works in 2000, 2005 and 2008 versions if date format is correct.
SELECT * FROM SpecialDay WHERE [Date]='2012-09-02'
But this is not safe. And starts from SQL server 2012 this query will not return anything even with the correct same time as in database records.
SELECT * FROM SpecialDay WHERE [Date]='2012-09-02 00:00:00.000'
Then I learned to do it better way:
DECLARE @date DATETIME;
SET @date = CONVERT(DATETIME, '2012-09-02', 120);
SELECT * FROM SpecialDay
WHERE [Date] >= @date
AND [Date] < DATEADD(d, 1, @date)
I tried it in all versions of SQL server starts from 2000 and works everywhere.
Here the statement
CONVERT(DATETIME, '2012-09-02', 120) converts a string to a date format type 120. This is so caled ODBC-format ‘yyyy-mm-dd hh:mm:ss’ 24-hours. String can be converted to Date-type can be converted with any separator character: slash, point or whatever.
Here is the list of supported date and time formats for CONVERT statement.