Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    19

    Post Unanswered: Need help with date function

    create function SvensktDatum2(@indate datetime, @separator char(1))
    returns nchar(20)
    as
    begin
    return
    convert(nvarchar(20), datepart(yyyy, @indate))
    + @separator
    declare @manad nvarchar(20)
    if (datepart(mm, @indate)between 1 and 6)
    begin
    set @manad ='0'+ (datepart(mm, @indate))
    + convert(nvarchar(20), @manad)
    end
    else
    begin
    set @manad = (datepart(mm, @indate))
    + convert(nvarchar(20), @manad)
    end

    + @separator

    declare @dag nvarchar(20)
    if (datepart(dd, @indate)between 1 and 6)
    begin
    set @dag ='0'+ (datepart(dd, @indate))
    + convert(nvarchar(20), @dag)
    end
    else
    begin
    set @dag = (datepart(dd, @indate))
    + convert(nvarchar(20), @dag)
    end
    end

    I get this message:

    Server: Msg 170, Level 15, State 1, Procedure SvensktDatum2, Line 20
    Line 20: Incorrect syntax near '+'.

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    is this what you mean?

    create function SvensktDatum2(
    @indate datetime, @separator char(1)) returns nchar(20)
    as begin
    declare @manad nvarchar(20), @dag nvarchar(20)
    set @manad = right('00' + cast((datepart(mm, @indate)) as varchar(2)), 2) + @separator
    set @dag = right('00' + cast((datepart(dd, @indate)) as varchar(2)), 2)
    return convert(nvarchar(20), datepart(yyyy, @indate)) + @separator + @manad + @dag
    end

  3. #3
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    19
    Well I dont get the result I wanted so I guess not. What I wanted was to display date like this 2003-05-05 instead of like this 2003-5-5. This is what the function looks like originally:

    create fuction SvensktDatum(@indate datetime, @separator char(1))
    returns nchar(20)
    as
    begin
    returns
    convert(nvarchar(20), datepart(yyyy, @indate))
    + @separator
    + convert(nvarchar(20), datepart(mm, @indate))
    + @separator
    + convert(nvarchar(20), datepart(dd, @indate))
    end

    So, I want to change the format of how to display the date but also give the user an option to show the year like 2003 or 03. Could someone help me with that?

    Originally posted by ms_sql_dba
    is this what you mean?

    create function SvensktDatum2(
    @indate datetime, @separator char(1)) returns nchar(20)
    as begin
    declare @manad nvarchar(20), @dag nvarchar(20)
    set @manad = right('00' + cast((datepart(mm, @indate)) as varchar(2)), 2) + @separator
    set @dag = right('00' + cast((datepart(dd, @indate)) as varchar(2)), 2)
    return convert(nvarchar(20), datepart(yyyy, @indate)) + @separator + @manad + @dag
    end

  4. #4
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    19

    Good so far

    OK, yes the first part worked, to format the date, but I also want to be able to let the user decide how to show the year. 2003 or 03



    Originally posted by buggirl
    Well I dont get the result I wanted so I guess not. What I wanted was to display date like this 2003-05-05 instead of like this 2003-5-5. This is what the function looks like originally:

    create fuction SvensktDatum(@indate datetime, @separator char(1))
    returns nchar(20)
    as
    begin
    returns
    convert(nvarchar(20), datepart(yyyy, @indate))
    + @separator
    + convert(nvarchar(20), datepart(mm, @indate))
    + @separator
    + convert(nvarchar(20), datepart(dd, @indate))
    end

    So, I want to change the format of how to display the date but also give the user an option to show the year like 2003 or 03. Could someone help me with that?

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    this call:

    select dbo.SvensktDatum2(getdate(), '-')

    will result in the following output:


    --------------------
    2003-09-26

    (1 row(s) affected)

  6. #6
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    19

    Yes

    I got that to the second time I tried. But I also want to give the user the option to choose weather year should be displayd 2003 or 03



    Originally posted by ms_sql_dba
    this call:

    select dbo.SvensktDatum2(getdate(), '-')

    will result in the following output:


    --------------------
    2003-09-26

    (1 row(s) affected)

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    then you need a third parameter passed that would indicate 2 or 4 digit value for the year, and modify the function above accordingly.

    alter function SvensktDatum2(
    @indate datetime, @separator char(1), @year_digits int) returns nchar(20)
    as begin
    declare @manad nvarchar(20), @dag nvarchar(20), @year nvarchar(10)
    set @manad = right('00' + cast((datepart(mm, @indate)) as varchar(2)), 2) + @separator
    set @dag = right('00' + cast((datepart(dd, @indate)) as varchar(2)), 2)
    if @year_digits = 2
    set @year = reverse(cast(reverse(ltrim(convert(nvarchar(20), datepart(yyyy, @indate)))) as char(2)))
    else
    set @year = convert(nvarchar(20), datepart(yyyy, @indate))
    return @year + @separator + @manad + @dag
    end

    then when you call it like this:

    select dbo.SvensktDatum2(getdate(), '-', 2)

    you'll get the following:


    --------------------
    03-09-26

    (1 row(s) affected)
    Last edited by ms_sql_dba; 09-26-03 at 21:35.

  8. #8
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    19
    Thank you so very much.
    Now it works just the way I want!




    Originally posted by ms_sql_dba
    then you need a third parameter passed that would indicate 2 or 4 digit value for the year, and modify the function above accordingly.

    alter function SvensktDatum2(
    @indate datetime, @separator char(1), @year_digits int) returns nchar(20)
    as begin
    declare @manad nvarchar(20), @dag nvarchar(20), @year nvarchar(10)
    set @manad = right('00' + cast((datepart(mm, @indate)) as varchar(2)), 2) + @separator
    set @dag = right('00' + cast((datepart(dd, @indate)) as varchar(2)), 2)
    if @year_digits = 2
    set @year = reverse(cast(reverse(ltrim(convert(nvarchar(20), datepart(yyyy, @indate)))) as char(2)))
    else
    set @year = convert(nvarchar(20), datepart(yyyy, @indate))
    return @year + @separator + @manad + @dag
    end

    then when you call it like this:

    select dbo.SvensktDatum2(getdate(), '-', 2)

    you'll get the following:


    --------------------
    03-09-26

    (1 row(s) affected)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •