sql語句中日期時間格式化查詢雪原虎 發布于： 2007-12-14 13:48
今天在做會員管理系統搜索時,我發現以前的搜索時間方式不太科學,效率也不是太高.由其是在查詢指定的時間相等的時候,我在數據庫中都存這樣的時間格式"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
