Convert code in MS Access to SQL server

Question

The following two functions, I would like to convert them from Access into SQL server, how can I moddify my code? Thank you so much. I never use SQL server before, and trying to learn it hard. LIS is a drive

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

When you're saying that you want to convert them, what do you want to convert them to? Use them inline, in a query? As a stored procedure? As a User-Defined Function? As bluefeet asked, what code have you tried so far, that hasn't worked? Have you googled for the search term 'monthname in sql server' at all? — Lynn Crumbling
– Lynn Crumbling, Commented May 23, 2012 at 14:28
Procedure's in SQL Server have a different structure to MS-Access, for instance they won't be "Public Functions", try googling "T-SQL" or "Transact SQL" as this is what SQL Server uses. — Matt Donnan
– Matt Donnan, Commented May 24, 2012 at 8:05
@bluefeet I tried to use the SSMA to convert the code, I thought that might work, however, it is not. I have no idea about VBA, and learning it now. Thank you for your help so far. — Maggie Mi
– Maggie Mi, Commented May 24, 2012 at 13:57
@LynnCrumbling Yes, I did google monthname. I was looking for a guidebook about VBA or about SQL server script — Maggie Mi
– Maggie Mi, Commented May 24, 2012 at 13:58
@MaggieMi: Don't limit yourself to thinking that you need to have functions declared somewhere in order to finesse the data as you're pulling it from the column. You can just as easily write T-SQL that returns the data as part of the query. Building off of Aaron's answer: SELECT CONVERT(CHAR(8), YOUR_DATE_FIELD, 112) as PathDate from YOUR_TABLE — Lynn Crumbling
– Lynn Crumbling, Commented May 24, 2012 at 14:20

Aaron Bertrand · Accepted Answer · 2012-05-23 14:40:06Z

2

In SQL Server, you don't have to do all that math to convert dates to ints and back.

First one:

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

Second one:

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

Example usage:

DECLARE @NDate DATETIME, @LISDate INT;

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

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

Results:

20120523        May 23, 2012

edited May 23, 2012 at 14:40

answered May 23, 2012 at 14:28

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert code in MS Access to SQL server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related