?
Solved

Date function displaying date without time

Posted on 2005-05-01
9
Medium Priority
?
213 Views
Last Modified: 2010-03-19
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
---------------------------------------------------------------------------------------------------------------------------------
0
Comment
Question by:zimmer9
9 Comments
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 13905960
Have you tried:

LEFT(GetDate(),10)
0
 
LVL 9

Expert Comment

by:dbeneit
ID: 13906009
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13906072
>you cant convert a string and cut it<<
Ouch!

Just use the CONVERT function as follows:

SELECT CONVERT(char(10), YourDateColum, 121)
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13906079
leew

>>Have you tried:

LEFT(GetDate(),10)<<

You may want to try this.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13906106
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
 
LVL 34

Expert Comment

by:arbert
ID: 13906132
agree with acperkins--use the built in functions to handle this.  Using left, substring, etc can be risky....
0
 

Author Comment

by:zimmer9
ID: 13906189
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 13906206
Yep, I missed it:
CONVERT(char(10), DATEADD(Month, -[MthTo], GETDATE() - Day(GETDATE())), 121)

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13906224
>>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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

864 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