Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can i return a date only format (DD/MM/YYYY) from a DateTime format column? example below

Modified Date
2007-09-01 02:00:00.000
2005-09-01 05:00:00.000

Output:

Date
09-01-2007
09-01-2005
share|improve this question
2  
You are asking for return a date only format (DD/MM/YYYY) but on your output that's not the format you show as it is MM-DD-YYYY with - instead of /. Which one it is? –  Yaroslav Jun 8 at 16:27
    
Convert SQL Server Date Time will help you. @ThomasStringer has kindly pointed you in right direction. –  Kin Jun 8 at 17:53
1  
BTW, if you don't care about the format and only want a date with the time part stripped, you can use something like SELECT CAST(GETDATE() as Date) –  dartonw Jun 9 at 3:02
add comment

2 Answers 2

If you are looking for a particular string formatting of a datetime data type value then you need to work with the convert() function with a particular style. In this case, you are looking for the style with a value of 110 (resulting in the format of mm-dd-yyyy):

select convert(char(10), getdate(), 110);

(Note: I use getdate() just to generate a datetime value, in which case you would just reference your datetime column) My output is this:

06-08-2014
share|improve this answer
    
Using your solution result is 2005-09-01 and 2007-09-01 but the OP is looking for 09-01-2007. I guess OP is bit confused on formats. –  Yaroslav Jun 8 at 16:24
add comment

Think you are looking for this:

SELECT CONVERT(VARCHAR(10), GETDATE(), 110)
SELECT CONVERT(VARCHAR(10), CONVERT(date, '2005-09-01 05:00:00.000', 120), 110)
SELECT CONVERT(VARCHAR(10), CONVERT(date, '2007-09-01 02:00:00.000', 120), 110)

Result:

06-08-2014
09-01-2005
09-01-2007

According to comments the inner CONVERT is not needed. But then, following OP question and provided sample data as Modified date the results don't match the Output suggested by him. Only if the OP is mistaken and all the time he is asking for datetime, as on the first sentence of his question.

enter image description here

Check this link for lot of samples on Using Date and Time Data

share|improve this answer
1  
Why do an inner convert of a datetime to a date before the outer conversion? No need for that. –  Thomas Stringer Jun 8 at 16:19
    
Indeed for the GETDATE() case there is no need of the inner CONVERT, edited. –  Yaroslav Jun 8 at 16:33
2  
@Yaroslav you don't need to convert any datetime to a date before converting to the varchar. Lines 2 and 3 contain unnecessary processing. –  Mark Sinkinson Jun 8 at 17:51
    
Please check my edit –  Yaroslav Jun 8 at 19:20
    
When you have a string literal as an argument to convert SQL Server has no way to know that you meant for it to be a datetime. That is why you need to convert to date (or datetime) first before you take back as a string in the expected format. The style 110 is ignored when you convert from a string to another string. The only thing that happens is that the string is truncated to 10 characters. Try this select convert(char(10), 'Truncated to ten', 110). It is not completely clear what data type OP is using but I interpret "DateTime format column" as the data type DateTime. –  Mikael Eriksson Jun 9 at 13:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.