Post reply

Need help regrading Query

  • December 8, 2004 at 7:01 am

    #164094

    I have one report based on this query.

    SELECT     dbo.TestLog.TestLogID, dbo.TestLog.SerialNumber, dbo.TestLog.TestDateTime,

                          dbo. TestLog.DUTCategory

    FROM         dbo.TestLog

    WHERE     (dbo.TestLog.TestDateTime >= @Beginning_Date) AND (dbo.TestLog.TestDateTime <= @Endind_Date+1) And

                          (dbo.DUTType.DUTType = @Type)

    I am passing two parameter one is Date and other is Type.

    I have two question regarding this query.

    1) on my table i store date as a smalldatetime formate "05/21/2004 10:00:00 AM' but in my query i want only date like "05/21/2004" how can i conver in to this formate.

    2) when i run this query i am passing two parameter one for date and other for Type. What i want a do. If i dont enter any value for Type parameter i want a run the query contain all type for given date. How can i do that.

     

  • December 8, 2004 at 7:10 am

    #533024

    1) CAST and CONVERT are your friends. Do a search here and you'll find countless threads on how to get rid of the time portion in a DATETIME column. And maybe this will get you going:

    SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0)

    SELECT CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112))

    SELECT CONVERT(CHAR(8),GETDATE(),112)

    SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)

    SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

    SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME)

    SELECT CAST(CAST(SUBSTRING(CAST(GETDATE() AS binary(8)),1,4) AS INT) AS DATETIME)

    2) You might want to read the explanation for CASE in BOL.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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