How to reformat getdate() data into mm/dd/ccyy and how to get todays date in mm/dd/ccyy format?

Posted on 2007-07-23
Last Modified: 2008-02-01
1. How do I  retrieve todays date in the format of mm/dd/ccyy
2 If I have a date that was stored using the getdate function in format mm/dd/ccyy hh:mm:ss am or pm,
   how do I retrieve only the mm/dd/ccyy portion of the date.

I am trying to read in a message of the day message that was meant to be displayed today?  I am using sql 2000.

SELECT     *
FROM         dbo.Message_of_the_Day
where pull only mm/dd/ccyy data from startdate >= todays date mm/dd/ccyy  and pull only mm/dd/ccyy data for enddate <=  todays date mm/dd/ccyy

startdate format of getdate mmdd/ccyy hh:mm:ss pm or am
enddate format of getdate mm/dd/ccyy hh:mm:ss pm or am
Question by:swansonplace
    LVL 4

    Accepted Solution

    To get a string:
    SELECT Convert(char(10), getdate(), 101)
    101 is the MM/DD/YYYY format. Read the help for "CAST and CONVERT" to learn about others
    To get a date without time (midnight):
    SELECT dateAdd(dd,dateDiff(dd,0,getdate()),0)

    If you store that (the result of dateAdd(...dateDiff()) in the DB, then you can query by doing simply saying WHERE myDateField='7/23/2007'
    If you store the whole date with time, you'll need to do something like either
    WHERE myDateField >='7/23/2007' AND myDateField<'7/24/2007'
    WHERE Year(myDateField ) = 2007 AND Month(myDateField ) = 7 and Day(myDateField )=23
    LVL 68

    Assisted Solution

    If startDate is never a future date -- which seems likely -- then:
    WHERE startDate >= CONVERT(CHAR(8), GETDATE(), 112)
    [Format 112 converts the date to yyyymmdd format; the time will default to 00:00, the start of the day.]

    If startDate could ever be a future date:
    WHERE startDate >= CONVERT(CHAR(8), GETDATE(), 112) AND startDate < CONVERT(CHAR(8), DATEADD(DAY, 1, GETDATE()), 112)

    Author Comment


    I ended up using both answers.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now