Post reply

Displaying Date from month and year

  • December 13, 2009 at 7:47 pm

    #217573

    Hi friends

    I have year and period values in my table like this

    2005 5

    2001 12

    2006 11

    I want to show 20050501,20011201,20061101

    any ideas please?

  • December 13, 2009 at 7:49 pm

    #1091738

    Are these stored in one field or multiple fields?

    separate fields, use the + to contatinate values

    select year + month + '01'

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 13, 2009 at 7:56 pm

    #1091743

    multiple fields,

    i tried your solution but its giving inaccurate results like this

    @year=2004

    @period=9

    select year+period+'01'

    result: 2014

    i wnat like this 20040900

  • December 13, 2009 at 8:00 pm

    #1091745

    If you are storing these as numerical values you will need to CAST them as strings.

    select cast( year as char(4)) + cast( period as char(2)) + '01'

    In the future, please include the DDL for the table.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 13, 2009 at 8:13 pm

    #1091748

    Thanks Steve..

    It works perfectly..awesome

    this is final code..

    cast( trn.TGJTYR as char(4)) + RTRIM(cast( trn.TGJTPD as char(2)))+ '01'

  • December 13, 2009 at 8:38 pm

    #1091753

    I wouldn't expect that to work...? You need to prefix with zeroes in some circumstances. One solution is:

    declare @year int,

    @month int;

    set @year = 2009;

    set @month = 1

    print right(10000 + @year, 4) + right(100 + @month, 2) + '01';

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • December 14, 2009 at 12:01 pm

    #1092143

    DECLARE @StartDate datetime, @EndDate datetime, @StartHour datetime, @EndHour datetime,

    @liTimeOffset INT

    SET @liTimeOffset = 120

    SELECT DateAdd(n, @liTimeOffset, LocationDateTime)

    FROM mytable

    WHERE

    LocationDateTime >= (CONVERT(VARCHAR(10), @StartDate, 120) + RIGHT(CONVERT(VARCHAR, @StartHour, 100),7))

    AND

    LocationDateTime < (CONVERT(VARCHAR(10), @EndDate, 120) + RIGHT(CONVERT(VARCHAR, @EndHour, 100),7))

    LocationDateTime = '2009-11-01 08:13:02' (this is an example).

    @StartHour and @EndHour is input parameters for stored procedure. When entering

    2009-11-29 (@StartDate), 2009-11-30 (@EndDate), 22:00 (@StartHour), 03:00 (@EndHour)

    the above works perfectly but I have one problem, if I want to use it as input parameters for SQL reporting services report it gives no result, if I specify @StartHour and @EndHour as datetime or Integer. If I specify as string it does return results, but more rows are returned that it should.

    How do I resolve issue ?

  • December 14, 2009 at 9:50 pm

    #1092344

    clive-421796 (12/14/2009)

    DECLARE @StartDate datetime, @EndDate datetime, @StartHour datetime, @EndHour datetime,

    @liTimeOffset INT

    SET @liTimeOffset = 120

    SELECT DateAdd(n, @liTimeOffset, LocationDateTime)

    FROM mytable

    WHERE

    LocationDateTime >= (CONVERT(VARCHAR(10), @StartDate, 120) + RIGHT(CONVERT(VARCHAR, @StartHour, 100),7))

    AND

    LocationDateTime < (CONVERT(VARCHAR(10), @EndDate, 120) + RIGHT(CONVERT(VARCHAR, @EndHour, 100),7))

    LocationDateTime = '2009-11-01 08:13:02' (this is an example).

    @StartHour and @EndHour is input parameters for stored procedure. When entering

    2009-11-29 (@StartDate), 2009-11-30 (@EndDate), 22:00 (@StartHour), 03:00 (@EndHour)

    the above works perfectly but I have one problem, if I want to use it as input parameters for SQL reporting services report it gives no result, if I specify @StartHour and @EndHour as datetime or Integer. If I specify as string it does return results, but more rows are returned that it should.

    How do I resolve issue ?

    DECLARE @StartDate datetime, @EndDate datetime, @StartHour datetime, @EndHour datetime,

    @liTimeOffset INT

    SET @liTimeOffset = 120

    Since all the parameters are datetime...

    SELECT DateAdd(n, @liTimeOffset, LocationDateTime)

    FROM mytable

    WHERE

    LocationDateTime >= @StartDate+@StartHour

    AND

    LocationDateTime < @EndDate+@EndHour

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 16, 2009 at 1:16 pm

    #1093277

    Thanks Jeff, your solution worked.

  • December 16, 2009 at 4:53 pm

    #1093355

    You're welcome. Thanks for the feedback, Clive.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 17, 2009 at 3:36 pm

    #1093799

    Hi Jeff, the query which I included in my post also worked.

    WHERE LocationDateTime >= (CONVERT(VARCHAR(10), @StartDate, 120) + RIGHT(CONVERT(VARCHAR, @StartHour, 100),7))

    AND LocationDateTime < (CONVERT(VARCHAR(10), @EndDate, 120) + RIGHT(CONVERT(VARCHAR, @EndHour, 100),7))

    In SQL Reports, you need to specify the @StartHour (19:00) and @EndHour (07:00) values as string (not datetime). This sorted the issue. I anyways used your method since its less code.

    Thanks again.

  • December 17, 2009 at 6:49 pm

    #1093838

    clive-421796 (12/17/2009)

    Hi Jeff, the query which I included in my post also worked.

    WHERE LocationDateTime >= (CONVERT(VARCHAR(10), @StartDate, 120) + RIGHT(CONVERT(VARCHAR, @StartHour, 100),7))

    AND LocationDateTime < (CONVERT(VARCHAR(10), @EndDate, 120) + RIGHT(CONVERT(VARCHAR, @EndHour, 100),7))

    In SQL Reports, you need to specify the @StartHour (19:00) and @EndHour (07:00) values as string (not datetime). This sorted the issue. I anyways used your method since its less code.

    Thanks again.

    Exactly... less code. Also, fewer conversions.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 17, 2009 at 7:00 pm

    #1093841

    Jeff Moden (12/17/2009)

    Exactly... less code. Also, fewer conversions.

    And more index-friendly 😉

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply