Solved

# Date function displaying date without time

Posted on 2005-05-01
Medium Priority
213 Views
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
---------------------------------------------------------------------------------------------------------------------------------

DateToC
2005-04-30 18:49:14.450
---------------------------------------------------------------------------------------------------------------------------------
0
Question by:zimmer9

LVL 97

Expert Comment

ID: 13905960
Have you tried:

LEFT(GetDate(),10)
0

LVL 9

Expert Comment

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

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

LVL 75

Expert Comment

ID: 13906079
leew

>>Have you tried:

LEFT(GetDate(),10)<<

You may want to try this.
0

LVL 75

Expert Comment

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

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

Author Comment

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 ?

DateToC
2005-04-30 18:49:14.450
0

LVL 75

Accepted Solution

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

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

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
Course of the Month17 days, 2 hours left to enroll