select records by Date in SQL Server

1, how to select

assume the table CAS.dbo._Test have a field AuthTime with the type datetime, We may use following SQL to select all the records in '2009-04-06'.

 

SELECT *

FROM CAS.dbo._Test

WHERE (AuthTime > CONVERT(DATETIME, '2009-04-06', 102)) AND

      (AuthTime < CONVERT(DATETIME, '2009-04-07', 102))

Or simply

SELECT *

FROM CAS.dbo._Test

WHERE (AuthTime > '2009-04-06') AND (AuthTime < '2009-04-07')

 

Here the string '2009-04-06' actually stands for '2009-04-06 AM 00:00:00'

Another example:

select CONVERT(VARCHAR(8),GETDATE(),112)

20090209

(1 row(s) affected)

 

2, some tips on getting time string

With the class COleDateTime you can convert the system time into a string with any form you like. The COleDateTime object can be initialed by time_t, SYSTEMTIME, or even:

COleDateTime time = COleDateTime(2001,6,15, 10, 8, 30);

If need the next day, just add 1 to the object. Call Format we can get the string form of time. Here is a example:

 

COleDateTime curTime;SYSTEMTIME time;CString cstrTimeFrom, cstrTimeTo;GetLocalTime(&time);m_curTime = time;cstrTimeFrom = curTime.Format("%Y-%m-%d");curTime += 1;cstrTimeTo = curTime.Format("%Y-%m-%d");

then the string cstrTimeFrom, cstrTimeTo are filled with '2009-04-06' and '2009-04-07'. The standard format: Format(_T("%Y-%m-%d %H:%M:%S"));

you may also get the right time string by MFC class CTime.

 

 

And there is some notes about the function CONVERT from MSDN:

Using CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments

expression

Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions.

data_type

Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types.

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.

style

Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports the date format in Arabic style, using Kuwaiti algorithm.

In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

 

Without century (yy)

With century (yyyy)

Standard

Input/Output**

-

0 or 100 (*)

Default

mon dd yyyy hh:miAM (or PM)

1

101

USA

mm/dd/yy

2

102

ANSI

yy.mm.dd

3

103

British/French

dd/mm/yy

4

104

German

dd.mm.yy

5

105

Italian

dd-mm-yy

6

106

-

dd mon yy

7

107

-

Mon dd, yy

8

108

-

hh:mm:ss

-

9 or 109 (*)

Default + milliseconds

mon dd yyyy hh:mi:ss:mmmAM (or PM)

10

110

USA

mm-dd-yy

11

111

JAPAN

yy/mm/dd

12

112

ISO

yymmdd

-

13 or 113 (*)

Europe default + milliseconds

dd mon yyyy hh:mm:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

-

20 or 120 (*)

ODBC canonical

yyyy-mm-dd hh:mi:ss(24h)

-

21 or 121 (*)

ODBC canonical (with milliseconds)

yyyy-mm-dd hh:mi:ss.mmm(24h)

-

126(***)

ISO8601

yyyy-mm-dd Thh:mm:ss.mmm(no spaces)

-

130*

Hijri****

dd mon yyyy hh:mi:ss:mmmAM

-

131*

Hijri****

dd/mm/yy hh:mi:ss:mmmAM

 

* The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
** Input when converting to datetime; output when converting to character data.
*** Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversion from real to character data, the output is equivalent to style 1.

Source:

http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx