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

3 Answers 3

up vote 0 down vote accepted

Here's some test sql for all the styles. Make nvarchar(max) shorter to trim (e.g. nvarchar(10)) .

    DECLARE @now datetime
    SET @now = GETDATE()
    select convert(nvarchar(MAX), @now, 0) as output, 0 as style 
    union select convert(nvarchar(MAX), @now, 1), 1
    union select convert(nvarchar(MAX), @now, 2), 2
    union select convert(nvarchar(MAX), @now, 3), 3
    union select convert(nvarchar(MAX), @now, 4), 4
    union select convert(nvarchar(MAX), @now, 5), 5
    union select convert(nvarchar(MAX), @now, 6), 6
    union select convert(nvarchar(MAX), @now, 7), 7
    union select convert(nvarchar(MAX), @now, 8), 8
    union select convert(nvarchar(MAX), @now, 9), 9
    union select convert(nvarchar(MAX), @now, 10), 10
    union select convert(nvarchar(MAX), @now, 11), 11
    union select convert(nvarchar(MAX), @now, 12), 12
    union select convert(nvarchar(MAX), @now, 13), 13
    union select convert(nvarchar(MAX), @now, 14), 14
    --15 to 19 not valid
    union select convert(nvarchar(MAX), @now, 20), 20
    union select convert(nvarchar(MAX), @now, 21), 21
    union select convert(nvarchar(MAX), @now, 22), 22
    union select convert(nvarchar(MAX), @now, 23), 23
    union select convert(nvarchar(MAX), @now, 24), 24
    union select convert(nvarchar(MAX), @now, 25), 25
    --26 not valid
    union select convert(nvarchar(MAX), @now, 100), 100
    union select convert(nvarchar(MAX), @now, 101), 101
    union select convert(nvarchar(MAX), @now, 102), 102
    union select convert(nvarchar(MAX), @now, 103), 103
    union select convert(nvarchar(MAX), @now, 104), 104
    union select convert(nvarchar(MAX), @now, 105), 105
    union select convert(nvarchar(MAX), @now, 106), 106
    union select convert(nvarchar(MAX), @now, 107), 107
    union select convert(nvarchar(MAX), @now, 108), 108
    union select convert(nvarchar(MAX), @now, 109), 109
    union select convert(nvarchar(MAX), @now, 110), 110
    union select convert(nvarchar(MAX), @now, 111), 111
    union select convert(nvarchar(MAX), @now, 112), 112
    union select convert(nvarchar(MAX), @now, 113), 113
    union select convert(nvarchar(MAX), @now, 114), 114
    union select convert(nvarchar(MAX), @now, 120), 120
    union select convert(nvarchar(MAX), @now, 121), 121
    --122 to 125 not valid
    union select convert(nvarchar(MAX), @now, 126), 126
    union select convert(nvarchar(MAX), @now, 127), 127
    --128, 129 not valid
    union select convert(nvarchar(MAX), @now, 130), 130
    union select convert(nvarchar(MAX), @now, 131), 131
    --132 not valid
    order BY style

Here's the result

output                   style
Apr 28 2014  9:31AM          0
04/28/14                     1
14.04.28                     2
28/04/14                     3
28.04.14                     4
28-04-14                     5
28 Apr 14                    6
Apr 28, 14                   7
09:31:28                     8
Apr 28 2014  9:31:28:580AM   9
04-28-14                     10
14/04/28                     11
140428                       12
28 Apr 2014 09:31:28:580     13
09:31:28:580                 14
2014-04-28 09:31:28          20
2014-04-28 09:31:28.580      21
04/28/14  9:31:28 AM         22
2014-04-28                   23
09:31:28                     24
2014-04-28 09:31:28.580      25
Apr 28 2014  9:31AM          100
04/28/2014                   101
2014.04.28                   102
28/04/2014                   103
28.04.2014                   104
28-04-2014                   105
28 Apr 2014                  106
Apr 28, 2014                 107
09:31:28                     108
Apr 28 2014  9:31:28:580AM   109
04-28-2014                   110
2014/04/28                   111
20140428                     112
28 Apr 2014 09:31:28:580     113
09:31:28:580                 114
2014-04-28 09:31:28          120
2014-04-28 09:31:28.580      121
2014-04-28T09:31:28.580      126
2014-04-28T09:31:28.580      127
28 جمادى الثانية 1435  9:31:28:580AM    130
28/06/1435  9:31:28:580AM    131
share|improve this answer

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

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

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.