1.select convert(smalldatetime,substring('510212800101213',7,6),112)
2.select convert(char(10),getdate(),108)
2.select convert(char(10),getdate(),108)
解决方案 »
- 小白的问题:in和=的区别
- 行转列+字符串拼接的问题
- 用SQL做个企业库存管理的数据库
- 比较难写的sql语句,帮忙
- 请问如何把HTML代码原封不动的存入数据库?我现在可以存进去,但"全都变成"
- 关于SQL Server 2000中Datetime型数据的格式??
- 两个数据库的问题?等待!
- (vfp6.0)请问如何在菜单中加上图标?vfp更高版本直接有这个功能吗?
- 关于变量的问题,再次提出。
- SELECT ... GROUP BY ... 真不能按记录数分组?
- 请教大虾:急!急!现需将一Excel库中的数据“倒入”SQLserver库中,不知该如何实现?
- 紧急求教,有关Lotus Domino!!!!!!
1。麻烦你解释一下语法和参数的用法
2。日期字段的值是 2000-1-1 23:12:15
怎样在where中只取2000-1-1
见下
No.2 is ok?
CONVERT provide similar functionality.
SyntaxUsing CAST:
CAST(expression AS data_type)
Using CONVERT:CONVERT (data_type[(length)], expression [, style])ArgumentsexpressionIs any valid Microsoft?SQL Server?expression. For more information, see
Expressions. data_typeIs the target system-supplied data type. User-defined data types cannot be used. For more information about available data types, see Data Types. lengthIs an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. styleIs the style of date format you want when converting 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).
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)
* 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.
Important By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE Automation objects, use a cutoff year of 2030. SQL Server provides a configuration option (two digit year cutoff) that changes the cutoff year used by SQL Server and allows the consistent treatment of dates. The safest course, however, is to specify four-digit years.When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when converting from datetime or smalldatetime values by using an appropriate char or varchar data type length.
This table shows the style values for float or real conversion to character data.Value Output
0 (the default) 6 digits maximum. Use in scientific notation, when appropriate.
1 Always 8 digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.
In the following table, the column on the left represents the style value for money or smallmoney conversion to character data.Value Output
0 (the default) No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.
1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.
2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.
where '时间' > '20:40:01'and '时间'<'21:10:10'
得出错误的结果,这是为何?
2. 要用如下语句: selece 姓名,部门,convert(char (8) ,日期,108) as 时间 from b考勤 where convert(char (8) ,日期,108) > '20:40:01' and convert(char (8) ,日期,108)<'21:10:10'因为SQL是先判断条件如计算出时间﹐Where中要求所有列均在表中﹐所以在条件中不可用计算列﹐但在order by 中则可以﹐因为order by是基于对结果集而言。