English 中文(简体)
SQL 服务器访问
原标题:Convert code in MS Access to SQL server

以下两个函数, 我想将其从 Access 转换为 SQL 服务器, 如何修改我的代码? 非常感谢。 我从来没有使用过 SQL 服务器, 并尝试用硬的学习。 LIS 是一个驱动器。

Public Function PathDate(Ndate As Date) As Long
    PathDate = (Year(Ndate) * 10000) + (Month(Ndate) * 100) + Day(Ndate)
End Function

Public Function NormalDate(LISDate As Long) As Date

    If (LISDate = -1) Then
        NormalDate = "-1"
    Else
        NormalDate = MonthName(LISDate  100 Mod 100) & " " & LISDate Mod 100 & " ," & LISDate  10000
    End If
End Function
问题回答

在 SQL 服务器中, 您不必做全部数学来转换日期为整数或背数 。

第一个:

CREATE FUNCTION dbo.PathDate
(
    @NDate DATETIME
)
RETURNS INT
AS
BEGIN
    RETURN (SELECT CONVERT(CHAR(8), @NDate, 112));
END
GO

第二个:

CREATE FUNCTION dbo.NormalDate
(
    @LISDate INT
)
RETURNS VARCHAR(32)
AS
BEGIN
    RETURN (SELECT DATENAME(MONTH, d) 
      +     + RTRIM(DAY(d)) 
      +  ,   + RTRIM(YEAR(d)) 
    FROM 
    (
      SELECT d = CONVERT(DATETIME, CONVERT(CHAR(8), @LISDate))
    ) AS d);
END
GO

实例使用:

DECLARE @NDate DATETIME, @LISDate INT;

SELECT @NDate = GETDATE(), @LISDate = 20120523;

SELECT dbo.PathDate(@NDate), dbo.NormalDate(@LISDate);

结果:

20120523        May 23, 2012




相关问题
Export tables from SQL Server to be imported to Oracle 10g

I m trying to export some tables from SQL Server 2005 and then create those tables and populate them in Oracle. I have about 10 tables, varying from 4 columns up to 25. I m not using any constraints/...

SQL server: Can NT accounts be mapped to SQL server accounts

In our database we have an SQL server account that has the correct roles to access some of the databases. We are now switching to windows authentication and I was wondering if we can create a NT user ...

SQL Server 2000, ADO 2.8, VB6

How to determine if a Transaction is active i.e. before issuing Begin Transaction I want to ensure that no previous transaction are open.. the platform is VB6, MS-SQL Server 2000 and ADO 2.8

热门标签