Date function displaying date without time

In the following 3 Date functions that follow, is there a way to yield a result that just displays the date without the time.

For example, instead of DFrom resulting in 2005-04-30 18:49:14.450, how would it be modified to display just 2005-04-30 ?

How would DateFromC result in 2004-11-01 ?

How would DateToC result in 2005-04-30 ?

--------------------------------------------------------------------------------------------------------------------------------
DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()) AS DFrom

DFrom
2005-04-30 18:49:14.450
---------------------------------------------------------------------------------------------------------------------------------
DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())) AS DateFromC

DateFromC      
204-11-01 18:49:14.450      
---------------------------------------------------------------------------------------------------------------------------------
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE())) AS DateToC

DateToC
2005-04-30 18:49:14.450
---------------------------------------------------------------------------------------------------------------------------------
zimmer9Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Yep, I missed it:
CONVERT(char(10), DATEADD(Month, -[MthTo], GETDATE() - Day(GETDATE())), 121)

0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Have you tried:

LEFT(GetDate(),10)
0
 
dbeneitCommented:
you cant convert a string and cut it

 right('0000' + rtrim(cast(DATEPART(yyyy, '01/03/2005 15:46') as char(4))),4)+'-'+right('00' + rtrim(cast(DATEPART(m,'01/03/2005 15:46') as char(2))),2)+ '-' + right('00' + rtrim(cast(DATEPART(d, '01/03/2005 15:46') as char(2))),2)
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Anthony PerkinsCommented:
>you cant convert a string and cut it<<
Ouch!

Just use the CONVERT function as follows:

SELECT CONVERT(char(10), YourDateColum, 121)
0
 
Anthony PerkinsCommented:
leew

>>Have you tried:

LEFT(GetDate(),10)<<

You may want to try this.
0
 
Anthony PerkinsCommented:
zimmer9,
Try it this way:

>>DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE()) AS DFrom<<
CONVERT(char(10), GETDATE() - Day(GETDATE()), 121) AS DFrom    -- This gives you the last day of the previous month. Incidentally, for the first day of the current month add 1, as in:
CONVERT(char(10), GETDATE() - Day(GETDATE()) + 1, 121) AS DFrom

>>DATEADD(M, [MthTo] * -1, DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())) AS DateFromC<<
DATEADD(Month, -[MthTo], GETDATE() - Day(GETDATE())) AS DateFromC
0
 
arbertCommented:
agree with acperkins--use the built in functions to handle this.  Using left, substring, etc can be risky....
0
 
zimmer9Author Commented:
DFrom works fine now using:
CONVERT(char(10), GETDATE() - Day(GETDATE()), 121) AS DFrom
displays for ex: 2005-04-30

DATEADD(Month, -[MthTo], GETDATE() - Day(GETDATE())) AS DateFromC
Wouldn't this need a convert statement as well ? It still seems to display the time and date.

displays for ex: 2005-10-30 20:31:38.187
---------------------------------------------------------------------------------------------------------------------
Do you know how I could convert this last date function to get rid of the time ?
DATEADD(M,[MthFrom]*-1,DATEADD(DD, DATEPART(DD, GETDATE()) * -1, GETDATE())) AS DateToC

DateToC
2005-04-30 18:49:14.450
0
 
Anthony PerkinsCommented:
>>Do you know how I could convert this last date function to get rid of the time ?<<

Try this:
DATEADD(Month, -MthFrom, GETDATE() - Day(GETDATE())) AS DateToC
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.