• sql语句中日期时间格式化查询


     今天在做会员管理系统搜索时,我发现以前的搜索时间方式不太科学,效率也不是太高.由其是在查询指定的时间相等的时候,我在数据库中都存这样的时间格式"2007-5-22 14:32:12"当我们在查询2007-5-22是否与它相等,结果是不相等的.

    所我们都喜欢找其它途径来解决这个问题。但是我发现我们的方法都不是太好,因为我们有的时候对sql sever

    的语句特性还了解的不够彻底。所以我查询了查sql server有帮助,终于发现个好东西,可以解决这个问题.

    CONVERT 函数,可以解决这个问题,能将2007-5-22 14:32:12这样的时间在查询或where 子句中格式化为:2007-5-22;

    如下:

    我将查询用户的注册时间 UserRegisterTime

    select * from [user] where UserActive=0 and replace(CONVERT(char(10),UserRegisterTime,111),'/','-')='2007-05-22' order by UserRegisterTime desc

    在这其中关键的一句

    replace(CONVERT(char(10),UserRegisterTime,111),'/','-');

    为什么我要replace将convert中的"/"符,换成"-"呢.是因为convert在这里格式化出来的时间是:2007/05/22

    还要特别说明一点:convert格式化出为的时间都是带有前有0的格式,比如2007-1-1 12:12:31结果为:

    2007-01-01,所以你在查询前务必要将你的日期中的月和日中小于10(不包括10)的数字加上前导0,

    如:1就应该为01

    此涵数的具体用法如下:

    转换函数

    当 Microsoft® SQL Server? 2000 不自动执行不同数据类型的表达式转换时,可使用转换函数 CAST 和 CONVERT 进行转换。这些转换函数还可用来获得各种特殊的数据格式。这两个转换函数都可用于选择列表、Where 子句和允许使用表达式的任何地方。

    如果希望 Transact-SQL 程序代码遵从 SQL-92 标准,请使用 CAST 而不要使用 CONVERT。如果要利用 CONVERT 中的样式功能,请使用 CONVERT 而不要使用 CAST。

    使用 CAST 或 CONVERT 时,需要两条信息:

    要转换的表达式(例如,销售报告要求销售数据从货币型数据转换为字符型数据)。

    要将所给表达式转换到的数据类型,例如,varchar 或 SQL Server 提供的任何其它数据类型。

    除非将被转换的值存储起来,否则转换仅在 CAST 或 CONVERT 函数的作用期内有效。

    在下面的示例中,第一个 Select 语句中使用 CAST,第二个 Select 语句中使用 CONVERT,将 title 列转换为 char(50) 列,以使结果更可读:

    USE pubs

    Select CAST(title AS char(50)), ytd_sales

    FROM titles

    Where type = 'trad_cook'

    USE pubs

    Select CONVERT(char(50), title), ytd_sales

    FROM titles

    Where type = 'trad_cook'

    下面是结果集:(对任何一个查询)

    ytd_sales

    ----------------------------------------- -----------

    Onions, Leeks, and Garlic: Cooking Secrets of the 375

    Fifty Years in Buckingham Palace Kitchens 15096

    Sushi, Anyone? 4095

    (3 row(s) affected)

    在下面的示例中,int 类型的ytd_sales 列转换为 char(20) 列,从而可以对该列使用 LIKE 谓词:

    USE pubs

    Select title, ytd_sales

    FROM titles

    Where CAST(ytd_sales AS char(20)) LIKE '15%'

    AND type = 'trad_cook'

    下面是结果集:

    Title ytd_sales

    ----------------------------------------- ---------

    Fifty Years in Buckingham Palace Kitchens 15096

    (1 row(s) affected)

    SQL Server 自动处理某些数据类型的转换。例如,如果比较 char 和 datetime 表达式、smallint 和 int 表达式、或不同长度的 char 表达式,SQL Server 可将它们自动转换。这种转换称为隐性转换。对这些转换不必使用 CAST 函数。但是,在下列情况下使用 CAST 都是可以接受的:

    两个表达式的数据类型完全相同。

    两个表达式可隐性转换。

    必须显式转换数据类型。

    如果试图进行不可能的转换(例如,将含有字母的 char 表达式转换为 int 类型),SQL Server 将显示一条错误信息。

    如果转换时没有指定数据类型的长度,则 SQL Server 自动提供长度为 30。

    转换为 datetime 或 smalldatetime 时,SQL Server 将拒绝所有无法识别为日期的值(包括 1753 年 1 月 1 日以前的日期)。当日期处于适当的范围内(1900 年 1 月 1 日到 2079 年 6 月 6 日)时,可将 datetime 值转换为 smalldatetime。时间值被四舍五入为最接近的分钟数。

    转换为 bit 将把任何非零值都更改为 1。

    转换为 money 或 smallmoney 时,整数后将加上货币单位。例如,整数值 4 被转换为货币时相当于 4 美元(对于默认语言 us_english)。浮点值的小数部分将四舍五入为四位小数以用于 money 值。将要转换为整数数据类型的 char 或 varchar 数据类型的表达式中,只能包含数字和可选的加号或减号(+ 或 -)。将忽略前导空格。要转换为 money 的 char 或 varchar 数据类型的表达式,还可包含可选的小数点和前导美元符号 ($)。

    要转换为 float 或 real 数据类型的 char 或 varchar 类型表达式还可选择性地包含指数符号(e 或 E,后面有可选的 + 或 – 符号,再后面是数字)。

    将字符表达式转换为其它大小的数据类型时,对于新的数据类型过长的值将被截断,SQL Server 将在 osql 实用工具和 SQL 查询分析器中显示星号 (*)。若数字表达式对于新的数据类型太长而无法显示,值将截断。下面是字符截断的示例:

    USE pubs

    Select SUBSTRING(title, 1, 25) AS Title, CONVERT(char(2), ytd_sales)

    FROM titles

    Where type = 'trad_cook'

    下面是结果集:

    Title

    ------------------------- --

    Onions, Leeks, and Garlic *

    Fifty Years in Buckingham *

    Sushi, Anyone? *

    (3 row(s) affected)

    在进行数据类型转换时,若目标数据类型的小数位数小于源数据类型的小数位数,则该值将被截断。例如,CAST(10.3496 AS money) 的结果是 $10.35。

    可将 text 数据显式转换为 char 或 varchar,将 image 显式转换为 binary 或 varbinary。由于这些数据类型被限制为 8,000 个字符,所以转换也限制为 character 和 binary 数据类型的最大长度,即 8,000 个字符。可将 ntext 数据显式转换为 nchar 或 nvarchar,但最大长度为 4,000 个字符。如果没有指定长度,被转换的值的默认长度为 30 个字符。不支持隐式转换。

    style 参数

    将 datetime 数据转换为 char 或 varchar 数据时,CONVERT 的 style 参数提供了各种日期显示格式。为 style 参数提供的数值确定了 datetime 数据的显示方式。年份可以显示为两位或四位数。默认情况下,SQL Server 将年份显示为两位数。若要显示包括世纪的四位数年份 (yyyy)(即使年份数据是使用两位数的年份格式存储的),请给 style 值加 100 以获得四位数年份。

     Convert的使用方法:

      格式:

    Convert(data_type,expression[,style])

      说明:此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)相互转换的时候才用到.

      例子:

    Select Convert(varchar(30),getdate(),101) now

      结果为

      now

      09/15/2001

      style数字在转换时间时的含义如下:

    Style(2位表示年份) Style(4位表示年份) 输入输出格式
    0 100 mon dd yyyy hh:miAM(或PM)
    1 101 mm/dd/yy
    2 102 yymmdd
    3 103 dd/mm/yy
    4 104 ddmmyy
    5 105 ddmmyy
    6 106 dd mon yy
    7 107 mon dd,yy
    8 108 hh:mm:ss
    9 109 mon dd yyyy hh:mi:ss:mmmmAM(或PM)
    10 110 mmddyy
    11 111 yy/mm/dd
    12 112 yymmdd
    13 113 dd mon yyyy hh:mi:ss:mmm(24小时制)
    14 114 hh:mi:ss:mmm(24小时制)
    20 120 yyyymmdd hh:mi:ss(24小时制)
    21 121 yyyymmdd hh:mi:ss:mmm(24小时制)

    以下示例显示用 style 参数进行转换:

    Select CONVERT(char(12), GETDATE(), 3)

    此语句把当前日期转换为样式 3,dd/mm/yy。

    在这里我附上一些实例:

    select CONVERT(varchar, getdate(), 120 )

    2004-09-12 11:06:08

    select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')

    20040912110608

    select CONVERT(varchar(12) , getdate(), 111 )

    2004/09/12

    select CONVERT(varchar(12) , getdate(), 112 )

    20040912

    select CONVERT(varchar(12) , getdate(), 102 )

    2004.09.12

    select CONVERT(varchar(12) , getdate(), 101 )

    09/12/2004

    select CONVERT(varchar(12) , getdate(), 103 )

    12/09/2004

    select CONVERT(varchar(12) , getdate(), 104 )

    12.09.2004

    select CONVERT(varchar(12) , getdate(), 105 )

    12-09-2004

    select CONVERT(varchar(12) , getdate(), 106 )

    12 09 2004

    select CONVERT(varchar(12) , getdate(), 107 )

    09 12, 2004

    select CONVERT(varchar(12) , getdate(), 108 )

    11:06:08

    select CONVERT(varchar(12) , getdate(), 109 )

    09 12 2004 1

    select CONVERT(varchar(12) , getdate(), 110 )

    09-12-2004

    select CONVERT(varchar(12) , getdate(), 113 )

    12 09 2004 1

    select CONVERT(varchar(12) , getdate(), 114 )

    11:06:08.177

  • 相关阅读:
    创建自动执行存储过程
    创建链接服务器
    SQLServer查询特殊符号处理
    SQL Server维护计划自动备份数据库
    SQL Server收缩数据库&列出所有表的数据条数
    SQL server日志文件过大处理方式
    SQL Server批量删除数据库表
    done apple经理面 匹配括号但是不能用stack
    done Beaconfire中国小哥中规中矩screening
    done marlabs挺难的screening
  • 原文地址:https://www.cnblogs.com/LiaoHao/p/3126519.html
Copyright © 2020-2023  润新知