Post reply

DateTime to Date only....

  • July 5, 2008 at 7:37 am

    #189209

    I am using the sample AdventureWorks database with the following query:

    SELECT [SalesOrderID]

    ,[RevisionNumber]

    ,[OrderDate]

    ,[DueDate]

    ,[ShipDate]

    ,[Status]

    FROM [AdventureWorks].[Sales].[SalesOrderHeader]

    The results i get are:

    4365912001-07-01 00:00:00.0002001-07-13 00:00:00.0002001-07-08 00:00:00.0005

    4366012001-07-01 00:00:00.0002001-07-13 00:00:00.0002001-07-08 00:00:00.0005

    4366112001-07-01 00:00:00.0002001-07-13 00:00:00.0002001-07-08 00:00:00.0005

    4366212001-07-01 00:00:00.0002001-07-13 00:00:00.0002001-07-08 00:00:00.0005

    What I am trying to do is to return the OrderDate data (2001-07-01 00:00:00.000) to Date only without the inclusion of time.

    Is there anyone who can help?

  • July 5, 2008 at 8:23 am

    #838500

    Try this to see if it gets you what you need;

    CONVERT(VARCHAR(10),[OrderDate],121)

    Cheers.

  • July 5, 2008 at 8:54 am

    #838501

    There was a very long forum post on various methods to get only the date portion from a datetime datatype. One of the most efficient was 1. subtract one-half of a day and subtract 2 milliseconds, which is 43200002 milliseconds.

    2. convert to an integer having the number of days since January 1, 1753. This conversion has implicit rounding, so that anything at noon or later is rounded to the next day (hence the subtracting of 1/2 day)

    3. convert the integer to a datetime data type.

    cast(cast(dateadd(ms,-43200002,MyDateTime) as integer)as datetime) as MyDate

    The various methods that used string manipulation used substantionally more resources and were slower.

    SQL = Scarcely Qualifies as a Language

  • July 5, 2008 at 9:28 am

    #838507

    Carl Federl (7/5/2008)

    There was a very long forum post on various methods to get only the date portion from a datetime datatype. One of the most efficient was 1. subtract one-half of a day and subtract 2 milliseconds, which is 43200002 milliseconds.

    2. convert to an integer having the number of days since January 1, 1753. This conversion has implicit rounding, so that anything at noon or later is rounded to the next day (hence the subtracting of 1/2 day)

    3. convert the integer to a datetime data type.

    cast(cast(dateadd(ms,-43200002,MyDateTime) as integer)as datetime) as MyDate

    The various methods that used string manipulation used substantionally more resources and were slower.

    Carl,

    Look at the OP's post... the times on all the dates are already in the midnight state... they don't need to be rounded down/truncated. The OP wants to know how to DISPLAY only the dates...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 5, 2008 at 9:30 am

    #838509

    "The OP wants to know how to DISPLAY only the dates... "

    Thanks Jeff, did not see that

    Of course the correct answer is to perform formating in the interface component, not in the database.

    Do you have a link to the long post about "date only" ?

    SQL = Scarcely Qualifies as a Language

  • July 5, 2008 at 9:36 am

    #838510

    b_boy...

    Along with what Mouse suggested, lookup CONVERT in Books Online to find all of the built-in date patterns. It's worth the read.

    As a side bar, if there's a GUI that this data is going to be provided to, the formatting shouldn't be done in SQL, it should be done in the GUI. There's a couple of reasons for this...

    1. Formatting doesn't take long but it does take extra time... let the client spend the time instead of the server.

    2. If you format it on the server side, then you're stuck (think global economy) with that format unless you make a conversion and formatted dates are rarely (except for ISO) sortable.

    3. If you need to do ANY date calculations, the you have to convert the formatted dates back to a Date/Time, do the calculations, and then reformat the dates.

    There're several other reasons having to do with performance and number of bytes transmitted on the pipe, but that should do. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 5, 2008 at 9:37 am

    #838511

    Looks like Carl beat me to the notation about formatting being done on the client side...

    Carl... no, I don't have the link that you're asking about... sorry.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 5, 2008 at 9:45 am

    #838513

    Take your choice of formats:

    SELECT [OrderID]

    ,CONVERT(VARCHAR(10),[OrderDate], 101) AS 'OrderDate'

    ,CONVERT(VARCHAR(10),[shippedDate],121) AS 'ShippedDate'

    ,CONVERT(VARCHAR(11),[RequiredDate], 106) AS 'RequiredDate'

    OrderId Order Date Shipped Date Required Date

    10248 07/04/1996 1996-07-16 01 Aug 1996

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • July 5, 2008 at 12:58 pm

    #838541

    BitBucket... I like your signature line... I think everyone should have that line in it... 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 7, 2008 at 2:43 am

    #838730

    Hey Guys

    Thanks for all your replies which has been very helpful, but when I tried it this way i got the following message, can anyone tell me what I doing that is wrong?

    USE DB_Reporting

    SELECT T_OrderLine.StockID, T_OrderLine.Quantity AS Qty, T_Items.Description,

    T_Items.CatalogueID AS Cat_ID, T_OrderLine.QuantityShipped AS [Qty Shipped],

    T_OrderLine.LineStatus AS Line_Status, T_OrderHeader.OrderID AS Order_ID,

    T_OrderHeader.CONVERT(varchar(11), OrderDate, 106),

    T_OrderHeader.OrderStatusID AS Order_Status_ID,

    T_OrderLine.BackOrdFlag AS BO_Flag, T_Items.AvailableLev AS [Stock Level]

    FROM (DB_Reporting.dbo.T_OrderHeader) [T_OrderHeader]

    INNER JOIN DB_Reporting.dbo.T_OrderLine [T_OrderLine]

    ON [T_OrderHeader].[OrderID]=[T_OrderLine].[OrderID]) INNER JOIN [DB_Reporting].[dbo].[T_Items] [T_Items]

    ON [T_OrderLine[.[StockID]=[T_Items].[StockID]

    WHERE [T_OrderLine].[LineStatus]=Y'N' AND [T_OrderHeader].[OrderDate]<{ts '2008-07-03 00:00:00'}

    AND [T_OrderHeader].[OrderStatusID]=Y'N'

    ORDER BY [T_OrderLine[.[LineStatus]

    DESC, [T_Items].[CatalogueID], [T_OrderLine].[StockID]

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

    Error msg received: Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'CONVERT'.

  • July 7, 2008 at 3:12 am

    #838732

    Thanks Guys for looking at it, but i got it fixed eventually after doing a little more digging, and i ended up using the following script:

    USE DB_Reporting

    SELECT T_OrderLine.StockID, T_OrderLine.Quantity AS Qty, T_Items.Description,

    T_Items.CatalogueID AS Cat_ID, T_OrderLine.QuantityShipped AS Qty_Shipped,

    T_OrderLine.LineStatus AS Line_Status, T_OrderHeader.OrderID AS Order_ID,

    CONVERT(varchar(11), T_OrderHeader.OrderDate, 106),

    T_OrderHeader.OrderStatusID AS Order_Status_ID,

    T_OrderLine.BackOrdFlag AS BO_Flag, [T_Items].[AvailableLev] AS [Stock Level]

    FROM (DB_Reporting.dbo.T_OrderHeader [T_OrderHeader]

  • July 7, 2008 at 3:12 am

    #838733

    Thanks Guys for looking at it, but i got it fixed eventually after doing a little more digging, and i ended up using the following script:

    USE DB_Reporting

    SELECT T_OrderLine.StockID, T_OrderLine.Quantity AS Qty, T_Items.Description,

    T_Items.CatalogueID AS Cat_ID, T_OrderLine.QuantityShipped AS Qty_Shipped,

    T_OrderLine.LineStatus AS Line_Status, T_OrderHeader.OrderID AS Order_ID,

    CONVERT(varchar(11), T_OrderHeader.OrderDate, 106),

    T_OrderHeader.OrderStatusID AS Order_Status_ID,

    T_OrderLine.BackOrdFlag AS BO_Flag, [T_Items].[AvailableLev] AS [Stock Level]

    FROM (DB_Reporting.dbo.T_OrderHeader [T_OrderHeader]

  • July 7, 2008 at 10:25 pm

    #839259

    Replace:

    T_OrderHeader.CONVERT(varchar(11), OrderDate, 106),

    With:

    CONVERT(varchar(11), T_OrderHeader.OrderDate, 106),

Viewing 13 posts - 1 through 12 (of 12 total)

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