Results 1 to 9 of 9

Thread: [RESOLVED] comprehensive DatePart use. why no go?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Location
    3rd rock from the sun
    Posts
    360

    Resolved [RESOLVED] comprehensive DatePart use. why no go?

    heya all

    trying to use the + operator, to tie 2gether two Date parts of one date,
    into 1 field .

    my query so far:
    VB Code:
    1. select isnull(datepart(mm,sdate),'')+' '+isnull(datepart(yy,sdate),'') as date
    2. ,count(unit) ts
    3. from saleslog
    4. WHERE sdate BETWEEN '01-01-2006 00:00:00' AND '1-31-2007 23:59:59'
    5. GROUP BY all isnull(datepart(mm,sdate),'')+' '+isnull(datepart(yy,sdate),'')
    6. ORDER BY isnull(datepart(mm,sdate),'')+' '+isnull(datepart(yy,sdate),'')

    other than selecting two datepart (1 for year,1 for month),
    how can i get a "DATEPART(mm+yy, sdate)"

    tnx in advance for replying.

    gooday,
    -j

  2. #2
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: comprehensive DatePart use. why no go?

    what is the database that you are using....
    If an answer to your question has been helpful, then please, Rate it!

    Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.


  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Location
    3rd rock from the sun
    Posts
    360

    Re: comprehensive DatePart use. why no go?

    Ms Sql 2000

  4. #4
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: comprehensive DatePart use. why no go?

    try
    Code:
    Select Month(sDate) + Year(sDate) As NewDate From YourTableName
    If an answer to your question has been helpful, then please, Rate it!

    Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.


  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Location
    3rd rock from the sun
    Posts
    360

    Re: comprehensive DatePart use. why no go?

    Quote Originally Posted by ganeshmoorthy
    try
    Code:
    Select Month(sDate) + Year(sDate) As NewDate From YourTableName
    cool. tnx for a quick reply man.

    didnt work, functions Month(Date) and Year(Date) , return integers.
    so for Jully,2006 comes back= 2013. (2006+7).

    managed to get around it though, Casted the fields to varchar... like so
    VB Code:
    1. select cast(Month(sdate) as varchar)+'\'+cast(Year(sdate) as varchar) as slDate,
    2. month(sdate) M, year(sdate) Y, count(unit) from saleslog
    3. WHERE sdate BETWEEN '02/01/2006 00:00:00' AND '01/23/2007 23:59:59'
    4. group by all cast(Month(sdate) as varchar)+'\'+cast(Year(sdate) as varchar),year(sdate),month(sdate)
    5. order by year(sdate) desc, month(sdate) desc, cast(Month(sdate) as varchar)+'\'+cast(Year(sdate) as varchar)



    thanks again for your help on this.

    regards,
    -j

  6. #6
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: comprehensive DatePart use. why no go?

    You must convert the datetime columns to varchar using convert or cast functions.

    I would recommend convert since it gives you the possibility to choose what format the datetime value should be converted to.

    Take a look i BOL for CONVERT. I could of course tell you how to do it, but you'll learn more by doing it yourself. All you need is a push in the correct direction -> CONVERT...

  7. #7
    VB Guru ganeshmoorthy's Avatar
    Join Date
    Dec 2005
    Location
    Sharjah, United Arab Emirates
    Posts
    3,031

    Re: comprehensive DatePart use. why no go?

    Im sorry, missed that cast part...anyway, its nice to know that it helped you...
    If an answer to your question has been helpful, then please, Rate it!

    Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.


  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: comprehensive DatePart use. why no go?

    MS SQL SERVER gives us two functions - DATEPART - which returns integers as you discovered...

    And DATENAME - which returns strings - that would be the method to use.

    Of course this will give you a MAY instead of 05 for month.

    Otherwise look at CONVERT - which is a nice way to change a date to a string and then you can LEFT() off of that or RIGHT() off of that return and get what you want.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jan 2006
    Location
    3rd rock from the sun
    Posts
    360

    Re: comprehensive DatePart use. why no go?

    Quote Originally Posted by szlamany
    MS SQL SERVER gives us two functions - DATEPART - which returns integers as you discovered...

    And DATENAME - which returns strings - that would be the method to use.

    Of course this will give you a MAY instead of 05 for month.

    Otherwise look at CONVERT - which is a nice way to change a date to a string and then you can LEFT() off of that or RIGHT() off of that return and get what you want.
    very good morning sZlamany,

    tnx for chiming on this 1 and for suggestions.


    nice day,
    regards,
    -j.

Posting Permissions

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



Click Here to Expand Forum to Full Width