Date & Time Formats

Format Query (current date: 8/30/2010) Sample
111 select convert(varchar, getdate(), 111) 8/30/2010
110 select convert(varchar, getdate(), 110) 8/30/2010
107 select convert(varchar, getdate(), 107) 30-Aug-10
106 select convert(varchar, getdate(), 106) 30-Aug-10
105 select convert(varchar, getdate(), 105) 30-8-2010
104 select convert(varchar, getdate(), 104) 30.8.2010
103 select convert(varchar, getdate(), 103) 30/8/2010
102 select convert(varchar, getdate(), 102) 2010.8.30
101 select convert(varchar, getdate(), 101) 8/30/2010
11 select convert(varchar, getdate(), 11) 6/8/1930
10 select convert(varchar, getdate(), 10) 8/30/2010
7 select convert(varchar, getdate(), 7) 30-Aug-10
6 select convert(varchar, getdate(), 6) 30-Aug-10
5 select convert(varchar, getdate(), 5) 30-8-06
4 select convert(varchar, getdate(), 4) 30.8.06
3 select convert(varchar, getdate(), 3) 30/8/06
2 select convert(varchar, getdate(), 2) 06.8.30
1 select convert(varchar, getdate(), 1) 8/30/2010

TIME FORMATS
8 or 108 select convert(varchar, getdate(), 8) 8:38:54
9 or 109 select convert(varchar, getdate(), 9) Dec 30 2010 8:38:54:840AM
14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840

MSDE

What is MSDE?
MSDE is a limited version of the Microsoft SQL Server. In short, it is the Microsoft SQL Server 2000 database engine without any of the fancy UI tools, and with some limitations in the database size and the number of connections. The MSDE database is free, and can be distributed embedded in your own applications or as a small stand alone SQL server. It is ideal for small websites and small businesses with less than 25 simultaneous users. The database is limited to 2 GB of data storage space, but you can easily upgrade it to a full Microsoft SQL Server without any limitations.

To login into MSDE there is a tool called osql.
-E -- Windows Authentication
-S -- ServerName
C:\> osql -E -Slocalhost\myinstance
1>select @@VERSION
2>go
3>quit

C:\ >osql -E -S localhost\myinstance
1> use master
2> go
1> select name from sysdatabases
2> go

To Login into MSDE using SQL Server Authentication

C:\> osql -Usa -Ppassword -Slocalhost\myinstance

To Detach Database
1> exec sp_detach_db 'mydatabase'
2> go

To Attach Database
1> exec sp_attach_db @dbname = 'mydatabase',
2> @filename1 =
'C:\Program Files\Microsoft SQL Server\MSSQL$LITBASE\Data\mydatabase.mdf',
3> @filename2 =
'C:\Program Files\Microsoft SQL Server\MSSQL$LITBASE\Data\mydatabase_log.LDF'
4> go

How to get missing numbers of a given sequence of numbers. Eg: 1,2,4,5,7,9,11,12 Output Should be : 3,6,8,10

create table Sequence_Numbers (
id int not null primary key
);

insert into Sequence_Numbers(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20);


select * from Sequence_Numbers

select l.id + 1 as start
from Sequence_Numbers as l
left outer join Sequence_Numbers as r on l.id + 1 = r.id
where r.id is null;