初学者求问

Question

例如有下面一个数据库
Bicycle tableBicycleID    Date
   1        2-01-2001
   2       12-03-2002
   3       21-03-2003
   4       01-05-2001
   5       02-02-2001
现在如果要搜索ID为2的bicycle但现实日期要为下面这种可以吗？就是把月份的数字改成string
BicycleID    Date
   2       12-March-2002

Answer 1

DECLARE @i DATETIME 
SET @i = '2001-09-02'
SET LANGUAGE us_english  --关键的地方
SELECT CONVERT(VARCHAR,day(@i))+'-'
+CONVERT(VARCHAR(3),datename(month,@i)) +'-'+CONVERT(VARCHAR,YEAR(@i))
--2-Sep-2001

Answer 2

CREATE FUNCTION [dbo].[F_MonthEn]
(
@date DATETIME
)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @month    INT
DECLARE @monthEn  VARCHAR(10)

SET @month = DATEPART(MONTH, @date)
SELECT @monthEn = CASE 
                       WHEN @month = 1 THEN 'January'
                       WHEN @month = 2 THEN 'February'
                       WHEN @month = 3 THEN 'March'
                       WHEN @month = 4 THEN 'April'
                       WHEN @month = 5 THEN 'May'
                       WHEN @month = 6 THEN 'June'
                       WHEN @month = 7 THEN 'July'
                       WHEN @month = 8 THEN 'August'
                       WHEN @month = 9 THEN 'September'
                       WHEN @month = 10 THEN 'October'
                       WHEN @month = 11 THEN 'November'
                       WHEN @month = 12 THEN 'December'
                  END

RETURN @monthEn
END
--------调用-------set dateformat dmy --设置日期格式
declare @date datetime
set @date='12-03-2002'select DATENAME(day, @date) + '-' + [dbo].[F_MonthEn](@date) + '-' + DATENAME(year, @date)

Answer 3

-----创建函数--------
CREATE FUNCTION [dbo].[F_MonthEn]
(
    @date DATETIME
)
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @month    INT
    DECLARE @monthEn  VARCHAR(10)
    
    SET @month = DATEPART(MONTH, @date)
    SELECT @monthEn = CASE 
                           WHEN @month = 1 THEN 'January'
                           WHEN @month = 2 THEN 'February'
                           WHEN @month = 3 THEN 'March'
                           WHEN @month = 4 THEN 'April'
                           WHEN @month = 5 THEN 'May'
                           WHEN @month = 6 THEN 'June'
                           WHEN @month = 7 THEN 'July'
                           WHEN @month = 8 THEN 'August'
                           WHEN @month = 9 THEN 'September'
                           WHEN @month = 10 THEN 'October'
                           WHEN @month = 11 THEN 'November'
                           WHEN @month = 12 THEN 'December'
                      END
    
    RETURN @monthEn
END
--------调用-------set dateformat dmy --设置日期格式
declare @date datetime
set @date='12-03-2002'select DATENAME(day, @date) + '-' + [dbo].[F_MonthEn](@date) + '-' + DATENAME(year, @date)-------结果----------
12-March-2002

Answer 4

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-09-29 09:14:42
-- Verstion:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
-- Apr 22 2011 11:57:00 
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([BicycleID] int,[Date] varchar(10))
insert [tb]
select 1,'2-01-2001' union all
select 2,'12-03-2002' union all
select 3,'21-03-2003' union all
select 4,'01-05-2001' union all
select 5,'02-02-2001'
--------------开始查询--------------------------
set LANGUAGE us_english  
set dateformat dmy  
select
  [BicycleID],
  ltrim(DAY(date))+'-'+DATENAME(mm,date)+'-'+ltrim(YEAR(date)) as date
from
  tb 
----------------结果----------------------------
/* Changed language setting to us_english.
BicycleID   date
----------- --------------------------------------------------------
1           2-January-2001
2           12-March-2002
3           21-March-2003
4           1-May-2001
5           2-February-2001(5 行受影响)*/

Answer 5

select
  BicycleID,
  ltrim(DAY(date))+'-'+DATENAME(mm,date)+'-'+ltrim(YEAR(date)) as Date
from
  tb