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

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
swansonplaceAsked:
Who is Participating?
 
bamboo7431Commented:
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'
or
WHERE Year(myDateField ) = 2007 AND Month(myDateField ) = 7 and Day(myDateField )=23
0
 
Scott PletcherSenior DBACommented:
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)
0
 
swansonplaceAuthor Commented:
thanks.

I ended up using both answers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.