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
Medium Priority
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

Accepted Solution

bamboo7431 earned 1000 total points
ID: 19550550
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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 19550896
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

ID: 19551359

I ended up using both answers.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

839 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