Post reply

QUERY

  • March 3, 2008 at 10:11 am

    #187269

    HI..

    TO GET THE WEEK IN A YEAR WE USE

    DATEPART(WK,GETDATE())

    ITS 10

    TO GET THE WEEK IN A MONTH WE USE

    select datediff(week, convert(varchar(6), getdate(), 112) + '01', getdate()) + 1

    ITS 2

    BUT WHATS THE LOGIC IN THAT I COULDNT UNDERSTAND ABOUT THE CONVERT AND THE '01'.GETDATE() AND +1 IN THE END AND I KNOW 112 IS THE STYLE IN THIS ....PLZ LET ME KNOW THKZ IN ADVANCE.....

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • March 3, 2008 at 10:28 am

    #784537

    The second query is saying to take the difference, in weeks, between the beginning of the month (3/1/2008) and today's date + 1 (3/4/2008).

    The + 1 at the end is to catch when the difference is 0. This way you do not have 0 weeks in the month.


    My blog: http://jahaines.blogspot.com

  • March 3, 2008 at 10:36 am

    #784543

    The second query is saying to take the difference, in weeks, between the beginning of the month (3/1/2008) and today's date + 1 (3/4/2008).

    The + 1 at the end is to catch when the difference is 0. This way you do not have 0 weeks in the month.

    select datediff(week, convert(varchar(6), getdate(), 112) + '01', getdate()) + 1

    so here in this where does it shows from the begining of this month + '01' is the one which says that way....n why did we use convet there ....i got some idea we r diff the weeks but where it mention from begining of this month ...plz let me know that would be gr8 thkz thanks in adavcne...n +1 issue i got an idea abt that ...

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • March 3, 2008 at 10:41 am

    #784548

    This piece of code creates the beginnning of month.

    select convert(varchar(6), getdate(), 112) + '01'


    My blog: http://jahaines.blogspot.com

  • March 3, 2008 at 10:42 am

    #784549

    The convert is used to take only the first 6 characters which are 200803. Then the query appends the one on the back to make the string 20080301.


    My blog: http://jahaines.blogspot.com

  • March 3, 2008 at 10:51 am

    #784550

    got it i understand now thkz a lot...adam..

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • March 3, 2008 at 2:24 pm

    #784676

    hey adam i have a doubt if i need to write the same logic using "cast" instead of convert how can i write that when i tried dnt that i got different output n wrong ones so culd u tell me plzzz...thkz in advance.....

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • March 3, 2008 at 2:29 pm

    #784678

    You cannot use cast and the proposed logic. The reason you need Convert is beacause it gives you the ability to format the date, while cast does not. For this code to work you have to use Convert.


    My blog: http://jahaines.blogspot.com

  • March 3, 2008 at 2:31 pm

    #784679

    You can use the following logic to get the same result.

    declare @dt datetime

    set @dt = DateAdd(month, DateDiff(month, 0, GETDATE()), 0)

    select datediff(week, @dt, getdate()) + 1


    My blog: http://jahaines.blogspot.com

  • March 3, 2008 at 2:37 pm

    #784681

    There is another way to do this, without using convert. The second select statement is equivalent to the first:

    select datediff(week, convert(varchar(6), getdate(), 112) + '01', getdate()) + 1

    select datediff(week, dateadd(mm,datediff(mm,0,getdate()),0), getdate()) + 1

    😎

    Edit: Adam, you beat me to it.

  • March 3, 2008 at 2:49 pm

    #784686

    🙂


    My blog: http://jahaines.blogspot.com

Viewing 11 posts - 1 through 11 (of 11 total)

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