Results 1 to 8 of 8

Thread: convert dates

  1. #1
    Join Date
    Oct 2006
    Posts
    55

    Unanswered: convert dates

    dear experts,
    i'm working in a website in asp.net where all the data is coming from stored procedures, i have also a callendar, now what i want is that when a client click a date i want to send this value to the stored procedure and the query the database according to that.
    i have a day, week, month selection, i did the following in C#
    date1(if the client click on a day)
    = Calendar1.SelectedDate.ToShortDateString(); this one is simple
    but i want to know how to convert the date for the week and the date for the month so the query knows which date and the query the database according to that date.
    for the week selection i have just the value stored in DateW = (just the week number) and for the month DateM = (month number 3) how can i convert these dates in sqlserver so the query knows for example if a month selected is march, then the C# value will be 3 and i want the query knows that this is march(from 01-03-2007 till 31-03-2007)?!!!
    any suggestions will be very appreciated.
    thanks experts.
    Peace out
    Mr joka

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't send the bits and pieces of the date string to SQL Server. Convert them to a valic datetime value in C# and then send that to the sproc.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2005
    Posts
    28
    Hi,

    For the first date and the last date of a month check the article at http://www.kodyaz.com/content/FirstA...ysOfMonth.aspx


    But also the following select may help you for both month and week

    SELECT
    firstdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
    lastdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1) ,
    firstdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0),
    lastdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)

    Eralper
    http://www.kodyaz.com

  4. #4
    Join Date
    Oct 2006
    Posts
    55
    thanks for the quick reply,
    actually it can not be converted from int to datetime in C# because in C# it is Gregorian calendar which gives you just the week number and the month number, what i want is if there is a way to take this value which is dateM = 3 which is march and convert it in the stored procedure so the query will know that this is march?!!!!
    thanks
    Peace out
    Mr joka

  5. #5
    Join Date
    Jan 2005
    Posts
    28
    Hi,

    Do you mean you have only the month and the year parameters?

    If so, please check the following sql code,

    declare @y int, @m int
    set @m = 12
    set @y = 2007

    SELECT
    firstdate_month =
    CAST(
    CAST(@y as varchar(4)) + '/' + CASE WHEN @m > 9 THEN CAST(@m as varchar(2)) ELSE '0' + CAST(@m as char(1)) END + '/01'
    as datetime
    ),
    lastdate_month =
    DATEADD(dd,-1,
    CAST(
    CAST(
    CASE WHEN @m < 12 THEN @y ELSE @y + 1 END as varchar(4)
    ) +
    '/' +
    CASE
    WHEN (@m+1 < 10) THEN '0' + CAST(@m+1 as char(1))
    WHEN (@m+1 > 9 and @m+1 < 12) THEN CAST(@m+1 as varchar(2))
    ELSE '01'
    END +
    '/01'
    as datetime
    )
    )


    Eralper
    http://www.kodyaz.com

  6. #6
    Join Date
    Oct 2006
    Posts
    55
    yes it is something like that, but i already have the query for the database and two parameters, the daystamp is one of them. how can i combine the SP i have and the one you give me? plus how do i need to do the week date, in C# i have just the week number, how can i tell to the SP which date to query the database for example dateW = 13 which is the week 13 of the year?!!
    thanks my friend for the help,
    good looking
    Peace out
    Mr joka

  7. #7
    Join Date
    Jan 2005
    Posts
    28
    Hi,

    I prepared the following script for the week problem. This is a little bit different when compared with the month version.

    But this has to be modified for dates if the week is not in the current year.


    declare @w int
    set @w = 1

    SELECT
    DATEADD(
    dd,
    (@w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
    DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
    ),
    DATEADD(
    dd,
    (@w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
    DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)
    )

    Eralper
    http://www.kodyaz.com

  8. #8
    Join Date
    Oct 2006
    Posts
    55
    thanks man for the help,
    last question i have already a stored procedure that have 2 parameters one of these parameter is the date, how can i combine the sp i already have with the 2 SP you gives me, the @w is the one i need to give to my stored procedure to query the date, so how can i send the value from the C# code convert it with your sp and then feed it to the my sp to query the database according to the converted date?!!!

    thanks
    Peace out
    Mr joka

Posting Permissions

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