Home Forums SQL Server 2005 Development split single row into four rows. RE: split single row into four rows.

  • April 9, 2013 at 4:29 am

    #1604747

    abhas (4/9/2013)

    Hi Matak/Chris,

    I want to use starttime and endtime as varchar. is it possible?

    Thanks

    Abhas.

    Yes:

    DECLARE @STARTDATE DATETIME, @StartTimeChar CHAR(5), @EndTimeChar CHAR(5)

    SELECT

    @STARTDATE = CAST(GETDATE() AS DATE),

    @StartTimeChar = '08:00',

    @EndTimeChar = '10:00'

    SELECT TOP(1+DATEDIFF(MINUTE,@StartTimeChar,@EndTimeChar)/15) -- number of rows to collect

    DATEADD(minute,((ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1)*15),StartDateTime)

    FROM sys.columns -- row source; could use tally table

    CROSS APPLY (

    SELECT StartDateTime =

    DATEADD(hour,CAST(LEFT(@StartTimeChar,2) AS INT),@STARTDATE)

    ) x

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden