locked
Formatting in date function RRS feed

  • Question

  • Hi, I am unable to get the correct results from the following query. The problem is in date formatting in WHERE clause.
        Please help in this query.

    DECLARE @Date1 as datetime
    Select  @Date1=getdate()-1

    Select Process_date from Trans where
    @date1=(Select max(Process_date) from Trans

    Note: The format of Process_date is '2009-07-31 00:00:00.000' in the table-Trans.

    Thanks.

    Wednesday, August 12, 2009 4:37 AM

Answers

  • Hi, I am unable to get the correct results from the following query. The problem is in date formatting in WHERE clause.
        Please help in this query.

    DECLARE @Date1 as datetime
    Select  @Date1=getdate()-1

    Select Process_date from Trans where
    @date1=(Select max(Process_date) from Trans

    Note: The format of Process_date is '2009-07-31 00:00:00.000' in the table-Trans.

    Thanks.

    Your query will give syntax error.

    There are many ways to do this. Depends on what you want actually.

    select top 1 Process_date
    from   Trans
    order by Process_date desc

    OR

    select Process_date
    from   Trans
    where  Process_date = (select max(Process_date) from Trans)

    OR

    for SQL 2005 / 2008
    select Process_date
    from
    (
        select Process_date, row_no = row_number() over (order by Process_date desc)
        from  Trans
    ) t
    where row_no = 1



    KH Tan
    • Marked as answer by Khan_K Wednesday, August 12, 2009 5:24 AM
    • Edited by K H Tan Wednesday, August 12, 2009 5:32 AM
    Wednesday, August 12, 2009 5:14 AM

All replies

  • What is the issue?
    Are you getting any error?
    No rows are retunred?

    Please provide some more details, if possible some sample data and expected output.
    Mangal Pardeshi
    SQL With Mangal
    Wednesday, August 12, 2009 4:57 AM
  • GETDATE() will return the current date AND time.  If I do GETDATE()-1 right now, I get a value of 2009-08-10 21:56:37.903.

    To zero out the time portion of the date, you can do the following:

    SET @ThisMorningAtMidnight = DATEADD(day,DATEDIFF(day,0,GETDATE()),0)   --Returns 2009-08-11 00:00:00.000

    So, for your @Date1 variable, you would just subtract a day from that:

    SET @Date1 = DATEADD(day,DATEDIFF(day,0,GETDATE()),0) - 1   --Returns 2009-08-10 00:00:00.000

    Does that help?

    --Brad (My Blog)
    Wednesday, August 12, 2009 5:00 AM
  • the Process_date is midnight so unless you run it at midnight it'll never work

    do you want to format @Date1 so its rounded to midnight as well?
    Wednesday, August 12, 2009 5:00 AM
  • Hi, I am unable to get the correct results from the following query. The problem is in date formatting in WHERE clause.
        Please help in this query.

    DECLARE @Date1 as datetime
    Select  @Date1=getdate()-1

    Select Process_date from Trans where
    @date1=(Select max(Process_date) from Trans

    Note: The format of Process_date is '2009-07-31 00:00:00.000' in the table-Trans.

    Thanks.

    Your query will give syntax error.

    There are many ways to do this. Depends on what you want actually.

    select top 1 Process_date
    from   Trans
    order by Process_date desc

    OR

    select Process_date
    from   Trans
    where  Process_date = (select max(Process_date) from Trans)

    OR

    for SQL 2005 / 2008
    select Process_date
    from
    (
        select Process_date, row_no = row_number() over (order by Process_date desc)
        from  Trans
    ) t
    where row_no = 1



    KH Tan
    • Marked as answer by Khan_K Wednesday, August 12, 2009 5:24 AM
    • Edited by K H Tan Wednesday, August 12, 2009 5:32 AM
    Wednesday, August 12, 2009 5:14 AM
  • Hi,
    Here is the more details.
    I am unable to get the results from the following query. The problem is in date formatting in WHERE clause because the script doesn't show any record. I am using SQL-Server 2000.
    Please help in this query.

    DECLARE @Date1 as datetime
    Select  @Date1=getdate()-1

    Select Process_date from Trans where
    @date1=(Select max(Process_date) from Trans  /*The problem is here. @Date1 has different date format
                                                                            and Process_data column has different date format*/

    Thanks.

    ---------Data of Table:Trans----------------

    Process_date                                            
    -----------------------
    2009-08-11 00:00:00.000
    2009-08-09 00:00:00.000
    2009-08-11 00:00:00.000
    2009-08-03 00:00:00.000

    --Output from the above query (Returning No rows)
    Process_date
    -------------

    --Expected Results
    Process_date                                            
    -----------------------
    2009-08-11 00:00:00.000
    2009-08-11 00:00:00.000

    Thanks.

    Wednesday, August 12, 2009 5:18 AM
  • Thanks for the solution.
    Wednesday, August 12, 2009 5:24 AM
  • datetime data type in table is stored in binary format.

    in your query, The data type for @Date and Process_date are both in datetime. So there isn't anything about mixmatch or different format.

    Not sure what is your intention of "Select  @Date1=getdate()-1"

    anyway try this
    select
     Process_date
    from Trans
    where Process_date = (select max (Process_date) from Trans)

    KH Tan
    Wednesday, August 12, 2009 5:32 AM
  • Hello,

    Try this

    DECLARE @Date1 as datetime
    Select  @Date1=getdate()-1
    
    
    DECLARE @Trans TABLE(Processdate DATETIME)
    
    INSERT INTO @Trans VALUES('2009-08-11 00:00:00.000')
    INSERT INTO @Trans VALUES('2009-08-09 00:00:00.000')
    INSERT INTO @Trans VALUES('2009-08-11 00:00:00.000')
    INSERT INTO @Trans VALUES('2009-08-03 00:00:00.000')
    SELECT *, CONVERT(NVARCHAR,Processdate,101) FROM @Trans
    SELECT @Date1, CONVERT(NVARCHAR,@date1,101)
    
    SELECT A.ProcessDate
    FROM @Trans AS A
    INNER JOIN (SELECT MAX(Processdate) AS ProcessDate FROM @Trans) AS B
    ON A.Processdate = B.ProcessDate
    WHERE CONVERT(NVARCHAR,B.ProcessDate,101) = CONVERT(NVARCHAR,@Date1,101)

    Pavan http://www.ggktech.com
    Wednesday, August 12, 2009 5:34 AM