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