Category Archives: databases

Using localdb in development

LocalDB is an instance of SQL Express. It is easy to use it in development from Visual Studio. I want to have localdb database files included into Visual Studio solution for easy connect to database wherever I work.

Sample solution can be downloaded here
Continue reading

Free .NET development software alternatives

The standard software stack for a .NET developer is

  • OS – desktop Windows
  • IDE – Visual Studio
  • Database – SQL Server

All these components are quite pricey. But there are free alternatives. And with my recent project I decided to use alternative software for development and production in .NET, all totally free.

  • OS – Linux Mint 17 (based on Debian/Ubuntu)
  • IDE – MonoDevelop
  • Database – MySQL Community Edition

Sample solution can be downloaded from here
Continue reading

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:

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.