Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Re: Missing record query

Expand Messages
  • John Warner
    Cool! Of course Paul s book was about SQL Server 2000 and yours 2005 +. I think I like the CTE better. John Warner
    Message 1 of 20 , Mar 27, 2010
    • 0 Attachment
      Cool! Of course Paul's 'book was about SQL Server 2000 and yours 2005 +.
      I think I like the CTE better.

      John Warner


      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
      > Sent: Saturday, March 27, 2010 5:41 AM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: Re: [SQLQueriesNoCode] Re: Missing record query
      >
      > Hi Paul,
      > Here is one solution which I got from another source; the difference being the
      > CTE structure.
      >
      > DECLARE @MARKET_PRICES TABLE (
      > PRICE_DATE DATETIME,
      > PRICE NUMERIC(18,2) );
      >
      > INSERT INTO @MARKET_PRICES SELECT '01/Mar/2010' , 0.45;
      > INSERT INTO @MARKET_PRICES SELECT '02/Mar/2010' , 0.44;
      > INSERT INTO @MARKET_PRICES SELECT '05/Mar/2010' , 0.43;
      > INSERT INTO @MARKET_PRICES SELECT '08/Mar/2010' , 0.40;
      > INSERT INTO @MARKET_PRICES SELECT '09/Mar/2010' , 0.41;
      > INSERT INTO @MARKET_PRICES SELECT '13/Mar/2010' , 0.46;
      > INSERT INTO @MARKET_PRICES SELECT '17/Mar/2010' , 0.44;
      >
      > WITH CTE(Date)AS(SELECT Date = CONVERT(DATETIME,'20100301')
      > UNION ALL SELECT Date = Date + 1 FROM CTE WHERE Date < '20100331'
      > )
      >
      > SELECT CTE.Date DisplayDate,
      > (SELECT MAX(aa.PRICE_DATE) FROM @MARKET_PRICES aa WHERE
      > aa.PRICE_DATE <= CTE.Date) PRICEDATE,
      > (SELECT MP.PRICE FROM @MARKET_PRICES MP WHERE
      > MP.PRICE_DATE = (SELECT MAX(aa.PRICE_DATE) FROM
      > @MARKET_PRICES aaWHERE aa.PRICE_DATE <= CTE.Date)) PRICE
      > FROM CTE
      > LEFT JOIN @MARKET_PRICES b
      > ON CTE.Date = b.PRICE_DATE
      > Best Wishes,
      > Noman Aftab
      >
      >
      > http://www.corpus.quran.com/wordbyword.jsp
      > http://www.jalandhari.qsh.eu
      >
      >
      >
      >
      >
      > ________________________________
      > From: p_livengood <p_livengood@...>
      > To: SQLQueriesNoCode@yahoogroups.com
      > Sent: Thu, 25 March, 2010 6:25:15 PM
      > Subject: [SQLQueriesNoCode] Re: Missing record query
      >
      >
      >
      >
      > sorry about that....is this better?
      >
      > ------CODE
      > --this is to recreate your table
      > DECLARE @MARKET_PRICES TABLE (
      > PRICE_DATE DATETIME,
      > PRICE NUMERIC(18,2) );
      >
      > INSERT INTO @MARKET_PRICES SELECT '01/Mar/2010' , 0.45;
      > INSERT INTO @MARKET_PRICES SELECT '02/Mar/2010' , 0.44;
      > INSERT INTO @MARKET_PRICES SELECT '05/Mar/2010' , 0.43;
      > INSERT INTO @MARKET_PRICES SELECT '08/Mar/2010' , 0.40;
      > INSERT INTO @MARKET_PRICES SELECT '09/Mar/2010' , 0.41;
      > INSERT INTO @MARKET_PRICES SELECT '13/Mar/2010' , 0.46;
      > INSERT INTO @MARKET_PRICES SELECT '17/Mar/2010' , 0.44;
      >
      > WITH --the with statement creates a table with a list of numbers...that is all
      > (taken from an old book i have)
      > G0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
      > G1 AS (SELECT 1 AS C FROM G0 AS A, G0 AS B),--4 rows
      > G2 AS (SELECT 1 AS C FROM G1 AS A, G1 AS B),--16 rows
      > G3 AS (SELECT 1 AS C FROM G2 AS A, G2 AS B),--256 rows
      > G4 AS (SELECT 1 AS C FROM G3 AS A, G3 AS B),--65536 rows
      > num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM G4)
      >
      > SELECT a.TempDate DisplayDate,
      > (SELECT MAX(aa.PRICE_ DATE) FROM @MARKET_PRICES aa WHERE
      > aa.PRICE_DATE <= a.TempDate) PRICEDATE,
      > (SELECT zz.PRICE FROM @MARKET_PRICES zz WHERE zz.PRICE_DATE
      > = (SELECT MAX(aa.PRICE_ DATE) FROM @MARKET_PRICES aa WHERE
      > aa.PRICE_DATE <= a.TempDate)) PRICE
      > FROM (SELECT CAST('2010-03- 01' AS DATETIME) + N-1 AS TempDate
      > --start date
      > FROM num
      > WHERE N BETWEEN 1 AND 31) a --number of days you want returned
      > LEFT JOIN @MARKET_PRICES b
      > ON a.TempDate = b.PRICE_DATE
      >
      > Paul
      >
      > --- In SQLQueriesNoCode@ yahoogroups. com, Noman Aftab <noman17pk@.
      > ..> wrote:
      > >
      > > Yeah, I also tried to make it run-able but was unable to do so.
      > > Best Wishes,
      > > Noman Aftab
      > >
      > >
      > >
      > >
      > >
      > >
      > >
      > > ____________ _________ _________ __
      > > From: John Warner <john@...>
      > > To: SQLQueriesNoCode@ yahoogroups. com
      > > Sent: Thu, 25 March, 2010 1:40:51 PM
      > > Subject: RE: [SQLQueriesNoCode] Missing record query
      > >
      > >
      > > Paul the list software screwed up your post so that it is extremely difficult to
      > read. If it isn't too much trouble (and you still have the original in your sent
      > items box) could you repost but before you do uncheck html?
      > >
      > > Thanks and if it is gone don't worry about it.
      > >
      > > John Warner
      > >
      > > > -----Original Message-----
      > > > From: SQLQueriesNoCode@ yahoogroups. com
      > > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of Paul
      > Livengood
      > > > Sent: Wednesday, March 24, 2010 9:32 PM
      > > > To: SQLQueriesNoCode@ yahoogroups. com
      > > > Subject: Re: [SQLQueriesNoCode] Missing record query
      > > >
      > > > There are many different ways to do this and the correct would to use is
      > based
      > > > on your business requirements (speed, how often used, how much data to
      > > > return, how called, etc).
      > > > I picked the following method because it uses no temp tables and is written
      > so
      > > > it can be in a table function if needed. I know it uses many sub-queries,
      > but if
      > > > your select list is low (a year of less would be my guess) then this will run
      > > > without issue.
      > > >
      > > >
      > > > HTH
      > > > Paul
      > > >
      > > > ------------ -CODE
      > > > --this is to recreate your tableDECLARE@ MARKET_PRICES
      > > > TABLE(PRICE_ DATE DATETIME,PRICE
      > > > NUMERIC(18,2) );INSERTINTO@ MARKET_PRICES
      > > > SELECT'01/Mar/ 2010',0.45; INSERTINTO@ MARKET_PRICES
      > > > SELECT'02/Mar/ 2010',0.44; INSERTINTO@ MARKET_PRICES
      > > > SELECT'05/Mar/ 2010',0.43; INSERTINTO@ MARKET_PRICES
      > > > SELECT'08/Mar/ 2010',0.40; INSERTINTO@ MARKET_PRICES
      > > > SELECT'09/Mar/ 2010',0.41; INSERTINTO@ MARKET_PRICES
      > > > SELECT'13/Mar/ 2010',0.46; INSERTINTO@ MARKET_PRICES
      > > > SELECT'17/Mar/ 2010',0.44; WITH--the with statement creates a table with
      > a list
      > > > of numbers...that is all (taken from an old book i have)G0 AS(SELECT1
      > ASC
      > > > UNION ALLSELECT1), --2 rowsG1 AS(SELECT1 ASC FROMG0 ASA,G0
      > > > ASB),--4 rowsG2 AS(SELECT1 ASC FROMG1 ASA,G1 ASB),--16 rowsG3
      > > > AS(SELECT1 ASC FROMG2 ASA,G2 ASB),--256 rowsG4 AS(SELECT1
      > ASC
      > > > FROMG3 ASA,G3 ASB),--65536 rowsnum
      > > > AS(SELECTROW_ NUMBER()OVER( ORDERBYC) ASN
      > > > FROMG4)SELECTa. TempDate
      > > > DisplayDate, (SELECTMAX( aa.PRICE_ DATE)FROM@
      > MARKET_PRICES aa
      > > > WHEREaa.PRICE_ DATE
      > > > <=a.TempDate) PRICEDATE, (SELECTzz. PRICE FROM@MARKET_
      > PRICES
      > > > zz WHEREzz.PRICE_ DATE
      > > > =(SELECTMAX( aa.PRICE_ DATE)FROM@ MARKET_PRICES aa
      > > > WHEREaa.PRICE_ DATE <=a.TempDate) )PRICEFROM( SELECTCAST(
      > '2010-
      > > > 03-01'ASDATETIME) +N-1 ASTempDate --start dateFROMnumWHEREN
      > > > BETWEEN1 AND31)a --number of days you want
      > > > returnedLEFTJOIN@ MARKET_PRICES bONa.TempDate
      > =b.PRICE_DATE
      > > >
      > > >
      > > >
      > > >
      > > > ____________ _________ _________ __
      > > > From: Noman Aftab <noman17pk@yahoo. com>
      > > > To: SQLQueriesNoCode@ yahoogroups. com
      > > > Cc: faraz.faheem@ hotmail.com
      > > > Sent: Wed, March 24, 2010 2:04:15 PM
      > > > Subject: [SQLQueriesNoCode] Missing record query
      > > >
      > > >
      > > > Hi,
      > > > I have a table MARKET_PRICES with columns: SYMBOL, PRICE_DATE
      > and
      > > > PRICE.
      > > > There may be days on which the prices don't get upload, simply because
      > they
      > > > havn't changed for the day.
      > > > Therefore data in the table can be like this:
      > > > 01/Mar/2010, 0.45
      > > > 02/Mar/2010, 0.44
      > > > 05/Mar/2010, 0.43
      > > > 08/Mar/2010, 0.40
      > > > 09/Mar/2010, 0.41
      > > > 13/Mar/2010, 0.46
      > > > 17/Mar/2010, 0.44
      > > >
      > > > I need a query which when given from and to dates, shows the prices for
      > each
      > > > day, if price of a particular day is not uploaded then the price of the
      > previous
      > > > day should be shown for that day, for e.g. giving dates 01/Mar/2010 to
      > > > 20/mar/2010 should yield the following results:
      > > >
      > > > 01/Mar/2010, 0.45
      > > > 02/Mar/2010, 0.44
      > > > 03/Mar/2010, 0.44
      > > > 04/Mar/2010, 0.44
      > > > 05/Mar/2010, 0.43
      > > > 06/Mar/2010, 0.43
      > > > 07/Mar/2010, 0.43
      > > > 08/Mar/2010, 0.40
      > > > 09/Mar/2010, 0.41
      > > > 10/Mar/2010, 0.41
      > > > 11/Mar/2010, 0.41
      > > > 12/Mar/2010, 0.41
      > > > 13/Mar/2010, 0.46
      > > > 14/Mar/2010, 0.46
      > > > 15/Mar/2010, 0.46
      > > > 16/Mar/2010, 0.46
      > > > 17/Mar/2010, 0.44
      > > > 18/Mar/2010, 0.44
      > > > 19/Mar/2010, 0.44
      > > > 20/Mar/2010, 0.44
      > > >
      > > > Best Wishes,
      > > > Noman Aftab
      > > >
      > > > http://www.corpus. quran.com/ wordbyword. jsp
      > > > http://www.jalandha ri.qsh.eu
      > > >
      > > > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!
      > > > http://downloads. yahoo.com/ in/internetexplo rer/
      > > >
      > > > [Non-text portions of this message have been removed]
      > > >
      > > >
      > > >
      > > >
      > > > [Non-text portions of this message have been removed]
      > > >
      > > >
      > > >
      > > > ------------ --------- --------- ------
      > > >
      > > > Yahoo! Groups Links
      > > >
      > > >
      > > >
      > >
      > >
      > >
      > >
      > >
      > > The INTERNET now has a personality. YOURS! See your Yahoo!
      > Homepage. http://in.yahoo. com/
      > >
      > > [Non-text portions of this message have been removed]
      > >
      >
      >
      >
      >
      >
      > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!
      > http://downloads.yahoo.com/in/internetexplorer/
      >
      >
      > ------------------------------------
      >
      > Yahoo! Groups Links
      >
      >
      >
    • hansve2000
      If using SQL Server 2005 or later: WITH cte AS (SELECT ROW_NUMBER OVER(PARTITION BY Col1, Col2, Col3 ORDER BY Col1) AS rw FROM dbo.Employees) DELETE FROM cte
      Message 2 of 20 , Apr 12, 2010
      • 0 Attachment
        If using SQL Server 2005 or later:

        WITH cte AS
        (SELECT ROW_NUMBER OVER(PARTITION BY Col1, Col2, Col3 ORDER BY Col1) AS rw
        FROM dbo.Employees)
        DELETE FROM cte
        WHERE rw > 1;

        Include all columns in the PARTITION BY list but any column can be used with the ORDER BY clause.

        Hans.

        --- In SQLQueriesNoCode@yahoogroups.com, arun aarun <saarun_emf@...> wrote:
        >
        > Dear Friends,
        >                                I have an employee table in which there is no primary key constraint  and there i have 600000 employees details . In that i have to delete duplicate records..
        >  
        >  
        > seeking for reply as early.....
        >  
        > Thanks&Regards,
        > Arun
        >
        >
        > The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
        >
        > [Non-text portions of this message have been removed]
        >
      • J P
        I have a table with three columns Month (values JAN,FEB,MAR,APR) etc Monthno (values 1-12) Year 2009,2010, etc   How do I take the above fields and create a
        Message 3 of 20 , Apr 15, 2010
        • 0 Attachment
          I have a table with three columns
          Month (values JAN,FEB,MAR,APR) etc
          Monthno (values 1-12)
          Year 2009,2010, etc
           
          How do I take the above fields and create a date that represents the first day of the month?

          i.e. Convert Jan 2009 to 1/1/2009
           
          I don't need hours:minutes:seconds
           
           
          Thanks for any suggestions.




          [Non-text portions of this message have been removed]
        • Arnie Rowland
          Something like this could work: cast(( cast( [MonthNo] as varchar(2)) + /01/ + cast( [Year] as varchar(4))) as date ) the date datatype is new with SQL
          Message 4 of 20 , Apr 15, 2010
          • 0 Attachment
            Something like this could work:

            cast(( cast( [MonthNo] as varchar(2)) + '/01/' + cast( [Year] as varchar(4))) as date )

            the 'date' datatype is new with SQL Server 2008. If you are using SQL Server 2005, cast as a datetime datatype instead.

            Regards,

            Arnie Rowland, MVP (SQL Server)

            "You cannot do a kindness too soon because you never know how soon it will be too late."
            -Ralph Waldo Emerson




            -----Original Message-----
            From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of J P
            Sent: Thursday, April 15, 2010 9:40 AM
            To: Arnie
            Subject: [SQLQueriesNoCode] converting to date time

            I have a table with three columns
            Month (values JAN,FEB,MAR,APR) etc
            Monthno (values 1-12)
            Year 2009,2010, etc

            How do I take the above fields and create a date that represents the first day of the month?

            i.e. Convert Jan 2009 to 1/1/2009

            I don't need hours:minutes:seconds


            Thanks for any suggestions.




            [Non-text portions of this message have been removed]



            ------------------------------------

            Yahoo! Groups Links







            Disclaimer - April 15, 2010
            This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
            This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
          • abhijit kakade
            HI , JHON     select convert(varchar, 1 + space(2) + [MONTH] + space(2) + [year], 102) from datetable Try this , this will be usefull. and let me know in
            Message 5 of 20 , Apr 15, 2010
            • 0 Attachment
              HI , JHON
               
               
              select convert(varchar, '1' + space(2) + [MONTH] + space(2) + [year], 102) from datetable

              Try this , this will be usefull. and let me know in case of any concern.
              I would like to help you.
               
              [month] , [year] its your column name of datetable table





              Thanks and Regards
              Abhijit B. Kakade
               
              M - +91 9226752508
              E  - abhijitkakade123@...




              Winning doesn't always mean being first ,winning means you're doing better than you've done before - Bonniie Blair

              --- On Thu, 15/4/10, J P <pulverizers73@...> wrote:


              From: J P <pulverizers73@...>
              Subject: [SQLQueriesNoCode] converting to date time
              To: SQLQueriesNoCode@yahoogroups.com
              Cc: john@...
              Date: Thursday, 15 April, 2010, 10:07 PM


               



              I have a table with three columns
              Month (values JAN,FEB,MAR, APR) etc
              Monthno (values 1-12)
              Year 2009,2010, etc
               
              How do I take the above fields and create a date that represents the first day of the month?

              i.e. Convert Jan 2009 to 1/1/2009
               
              I don't need hours:minutes: seconds
               
               
              Thanks for any suggestions.

              [Non-text portions of this message have been removed]










              [Non-text portions of this message have been removed]
            • J P
              that got it, thanks Arnie. 2005 version here, so I used datetime. ________________________________ From: Arnie Rowland To:
              Message 6 of 20 , Apr 15, 2010
              • 0 Attachment
                that got it, thanks Arnie.

                2005 version here, so I used datetime.




                ________________________________
                From: Arnie Rowland <arnie@...>
                To: SQLQueriesNoCode@yahoogroups.com
                Sent: Thu, April 15, 2010 11:57:24 AM
                Subject: RE: [SQLQueriesNoCode] converting to date time

                 
                Something like this could work:

                cast(( cast( [MonthNo] as varchar(2)) + '/01/' + cast( [Year] as varchar(4))) as date )

                the 'date' datatype is new with SQL Server 2008. If you are using SQL Server 2005, cast as a datetime datatype instead.

                Regards,

                Arnie Rowland, MVP (SQL Server)

                "You cannot do a kindness too soon because you never know how soon it will be too late."
                -Ralph Waldo Emerson

                -----Original Message-----
                From: SQLQueriesNoCode@ yahoogroups. com [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of J P
                Sent: Thursday, April 15, 2010 9:40 AM
                To: Arnie
                Subject: [SQLQueriesNoCode] converting to date time

                I have a table with three columns
                Month (values JAN,FEB,MAR, APR) etc
                Monthno (values 1-12)
                Year 2009,2010, etc

                How do I take the above fields and create a date that represents the first day of the month?

                i.e. Convert Jan 2009 to 1/1/2009

                I don't need hours:minutes: seconds


                Thanks for any suggestions.

                [Non-text portions of this message have been removed]

                ------------ --------- --------- ------

                Yahoo! Groups Links

                Disclaimer - April 15, 2010
                This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@ yahoogroups. com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                This disclaimer was added by Policy Patrol: http://www.policypatrol.com/






                [Non-text portions of this message have been removed]
              • John Warner
                Arnie as I look at this I wonder if DATEADD(mm,DATEDIFF(mm,0,theDate),0 Wouldn t work to get the 01 for the day of any passed in date in place of the CASTING?
                Message 7 of 20 , Apr 15, 2010
                • 0 Attachment
                  Arnie as I look at this I wonder if

                  DATEADD(mm,DATEDIFF(mm,0,theDate),0

                  Wouldn't work to get the 01 for the day of any passed in date in place of
                  the CASTING? I guess really which would be more efficient to the server
                  cpu?

                  John Warner


                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                  > Sent: Thursday, April 15, 2010 12:57 PM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: RE: [SQLQueriesNoCode] converting to date time
                  >
                  > Something like this could work:
                  >
                  > cast(( cast( [MonthNo] as varchar(2)) + '/01/' + cast( [Year] as
                  varchar(4))) as
                  > date )
                  >
                  > the 'date' datatype is new with SQL Server 2008. If you are using SQL
                  Server
                  > 2005, cast as a datetime datatype instead.
                  >
                  > Regards,
                  >
                  > Arnie Rowland, MVP (SQL Server)
                  >
                  > "You cannot do a kindness too soon because you never know how soon it
                  will
                  > be too late."
                  > -Ralph Waldo Emerson
                  >
                  >
                  >
                  >
                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of J P
                  > Sent: Thursday, April 15, 2010 9:40 AM
                  > To: Arnie
                  > Subject: [SQLQueriesNoCode] converting to date time
                  >
                  > I have a table with three columns
                  > Month (values JAN,FEB,MAR,APR) etc
                  > Monthno (values 1-12)
                  > Year 2009,2010, etc
                  >
                  > How do I take the above fields and create a date that represents the
                  first day of
                  > the month?
                  >
                  > i.e. Convert Jan 2009 to 1/1/2009
                  >
                  > I don't need hours:minutes:seconds
                  >
                  >
                  > Thanks for any suggestions.
                  >
                  >
                  >
                  >
                  > [Non-text portions of this message have been removed]
                  >
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                  >
                  >
                  >
                  >
                  > Disclaimer - April 15, 2010
                  > This email and any files transmitted with it are confidential and
                  intended solely
                  > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                  > addressee you should not disseminate, distribute, copy or alter this
                  email. Any
                  > views or opinions presented in this email are solely those of the author
                  and
                  > might not represent those of Westwood Consulting, Inc. Warning: Although
                  > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                  > viruses are present in this email, the company cannot accept
                  responsibility for
                  > any loss or damage arising from the use of this email or attachments.
                  > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                • Arnie Rowland
                  It would be a good alternative -IF theDate was in a date format. In this situation, you have to build the date string first. So, it could be used for the
                  Message 8 of 20 , Apr 15, 2010
                  • 0 Attachment
                    It would be a good alternative -IF 'theDate' was in a date format.

                    In this situation, you have to build the date string first. So, it could
                    be used for the outer CAST().

                    It's questionable if involving two functions (dateadd() and datediff())
                    would be more efficient than a single cast(). And there still is an
                    implicit cast() from the built up string to a datetime datatype in order
                    to use the date functions. I'm not convinced it would be more efficient.

                    I guess it depends upon the usage. AS with everything SQL Server, test,
                    test, test.

                    Regards,

                    Arnie Rowland, MVP (SQL Server)

                    "You cannot do a kindness too soon because you never know how soon it
                    will be too late."
                    -Ralph Waldo Emerson




                    -----Original Message-----
                    From: SQLQueriesNoCode@yahoogroups.com
                    [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                    Sent: Thursday, April 15, 2010 10:10 AM
                    To: Arnie
                    Subject: RE: [SQLQueriesNoCode] converting to date time

                    Arnie as I look at this I wonder if

                    DATEADD(mm,DATEDIFF(mm,0,theDate),0

                    Wouldn't work to get the 01 for the day of any passed in date in place
                    of the CASTING? I guess really which would be more efficient to the
                    server cpu?

                    John Warner


                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                    > Sent: Thursday, April 15, 2010 12:57 PM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: RE: [SQLQueriesNoCode] converting to date time
                    >
                    > Something like this could work:
                    >
                    > cast(( cast( [MonthNo] as varchar(2)) + '/01/' + cast( [Year] as
                    varchar(4))) as
                    > date )
                    >
                    > the 'date' datatype is new with SQL Server 2008. If you are using SQL
                    Server
                    > 2005, cast as a datetime datatype instead.
                    >
                    > Regards,
                    >
                    > Arnie Rowland, MVP (SQL Server)
                    >
                    > "You cannot do a kindness too soon because you never know how soon it
                    will
                    > be too late."
                    > -Ralph Waldo Emerson
                    >
                    >
                    >
                    >
                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of J P
                    > Sent: Thursday, April 15, 2010 9:40 AM
                    > To: Arnie
                    > Subject: [SQLQueriesNoCode] converting to date time
                    >
                    > I have a table with three columns
                    > Month (values JAN,FEB,MAR,APR) etc
                    > Monthno (values 1-12)
                    > Year 2009,2010, etc
                    >
                    > How do I take the above fields and create a date that represents the
                    first day of
                    > the month?
                    >
                    > i.e. Convert Jan 2009 to 1/1/2009
                    >
                    > I don't need hours:minutes:seconds
                    >
                    >
                    > Thanks for any suggestions.
                    >
                    >
                    >
                    >
                    > [Non-text portions of this message have been removed]
                    >
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    > Disclaimer - April 15, 2010
                    > This email and any files transmitted with it are confidential and
                    intended solely
                    > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                    > addressee you should not disseminate, distribute, copy or alter this
                    email. Any
                    > views or opinions presented in this email are solely those of the
                    > author
                    and
                    > might not represent those of Westwood Consulting, Inc. Warning:
                    > Although Westwood Consulting, Inc has taken reasonable precautions to
                    > ensure no viruses are present in this email, the company cannot accept
                    responsibility for
                    > any loss or damage arising from the use of this email or attachments.
                    > This disclaimer was added by Policy Patrol:
                    > http://www.policypatrol.com/
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >



                    ------------------------------------

                    Yahoo! Groups Links
                  • John Warner
                    Good point. Thanks for the explanation, I always learn something when I ask you or some of the others here. John Warner
                    Message 9 of 20 , Apr 15, 2010
                    • 0 Attachment
                      Good point. Thanks for the explanation, I always learn something when I
                      ask you or some of the others here.

                      John Warner


                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                      > Sent: Thursday, April 15, 2010 1:18 PM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: RE: [SQLQueriesNoCode] converting to date time
                      >
                      > It would be a good alternative -IF 'theDate' was in a date format.
                      >
                      > In this situation, you have to build the date string first. So, it could
                      > be used for the outer CAST().
                      >
                      > It's questionable if involving two functions (dateadd() and datediff())
                      > would be more efficient than a single cast(). And there still is an
                      > implicit cast() from the built up string to a datetime datatype in order
                      > to use the date functions. I'm not convinced it would be more efficient.
                      >
                      > I guess it depends upon the usage. AS with everything SQL Server, test,
                      > test, test.
                      >
                      > Regards,
                      >
                      > Arnie Rowland, MVP (SQL Server)
                      >
                      > "You cannot do a kindness too soon because you never know how soon it
                      > will be too late."
                      > -Ralph Waldo Emerson
                      >
                      >
                      >
                      >
                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                      > Sent: Thursday, April 15, 2010 10:10 AM
                      > To: Arnie
                      > Subject: RE: [SQLQueriesNoCode] converting to date time
                      >
                      > Arnie as I look at this I wonder if
                      >
                      > DATEADD(mm,DATEDIFF(mm,0,theDate),0
                      >
                      > Wouldn't work to get the 01 for the day of any passed in date in place
                      > of the CASTING? I guess really which would be more efficient to the
                      > server cpu?
                      >
                      > John Warner
                      >
                      >
                      > > -----Original Message-----
                      > > From: SQLQueriesNoCode@yahoogroups.com
                      > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie
                      > Rowland
                      > > Sent: Thursday, April 15, 2010 12:57 PM
                      > > To: SQLQueriesNoCode@yahoogroups.com
                      > > Subject: RE: [SQLQueriesNoCode] converting to date time
                      > >
                      > > Something like this could work:
                      > >
                      > > cast(( cast( [MonthNo] as varchar(2)) + '/01/' + cast( [Year] as
                      > varchar(4))) as
                      > > date )
                      > >
                      > > the 'date' datatype is new with SQL Server 2008. If you are using SQL
                      > Server
                      > > 2005, cast as a datetime datatype instead.
                      > >
                      > > Regards,
                      > >
                      > > Arnie Rowland, MVP (SQL Server)
                      > >
                      > > "You cannot do a kindness too soon because you never know how soon it
                      > will
                      > > be too late."
                      > > -Ralph Waldo Emerson
                      > >
                      > >
                      > >
                      > >
                      > > -----Original Message-----
                      > > From: SQLQueriesNoCode@yahoogroups.com
                      > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of J P
                      > > Sent: Thursday, April 15, 2010 9:40 AM
                      > > To: Arnie
                      > > Subject: [SQLQueriesNoCode] converting to date time
                      > >
                      > > I have a table with three columns
                      > > Month (values JAN,FEB,MAR,APR) etc
                      > > Monthno (values 1-12)
                      > > Year 2009,2010, etc
                      > >
                      > > How do I take the above fields and create a date that represents the
                      > first day of
                      > > the month?
                      > >
                      > > i.e. Convert Jan 2009 to 1/1/2009
                      > >
                      > > I don't need hours:minutes:seconds
                      > >
                      > >
                      > > Thanks for any suggestions.
                      > >
                      > >
                      > >
                      > >
                      > > [Non-text portions of this message have been removed]
                      > >
                      > >
                      > >
                      > > ------------------------------------
                      > >
                      > > Yahoo! Groups Links
                      > >
                      > >
                      > >
                      > >
                      > >
                      > >
                      > >
                      > > Disclaimer - April 15, 2010
                      > > This email and any files transmitted with it are confidential and
                      > intended solely
                      > > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                      > > addressee you should not disseminate, distribute, copy or alter this
                      > email. Any
                      > > views or opinions presented in this email are solely those of the
                      > > author
                      > and
                      > > might not represent those of Westwood Consulting, Inc. Warning:
                      > > Although Westwood Consulting, Inc has taken reasonable precautions to
                      > > ensure no viruses are present in this email, the company cannot accept
                      > responsibility for
                      > > any loss or damage arising from the use of this email or attachments.
                      > > This disclaimer was added by Policy Patrol:
                      > > http://www.policypatrol.com/
                      > >
                      > >
                      > > ------------------------------------
                      > >
                      > > Yahoo! Groups Links
                      > >
                      > >
                      > >
                      >
                      >
                      >
                      > ------------------------------------
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      > ------------------------------------
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                    Your message has been successfully submitted and would be delivered to recipients shortly.