Tag Archives: SQL

How to avoid division by zero in MSSQL

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

 

How to compare date in MS SQL

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:

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.