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).

* 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

最后

以上就是魁梧水杯为你收集整理的select records by Date in SQL Server的全部内容，希望文章能够帮你解决select records by Date in SQL Server所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。