0

I am trying to convert a datetime, to a varchar exactly as is.

So I would like the following:

SELECT CONVERT(varchar(30),'2014-10-21 12:02:22.555')

To return exactly 2014-10-21 12:02:22.555 every time. I have researched this a bit, but I don't see a difference in the options in converting a full date to varchar like this. Will this work with all dates just using the basic convert(varchar(30), with no option at the end?

mameesh
  • 3,651
  • 9
  • 37
  • 47
  • Why do you need to do this? This sounds like it should be a concern for your presentation code rather than for SQL. – Becuzz Oct 21 '14 at 17:19
  • 1
    In your question, you are converting a string to `VARCHAR(30)`, which makes no sense (or rather, it does nothing). Do you mean to convert a string to `DATETIME`, or vice versa? – Jeroen Mostert Oct 21 '14 at 17:21
  • I am actually using it within the hashbytes function which does not support passing in a datetime so I need to convert it. SELECT HASHBYTES('md5',Datetime) fails – mameesh Oct 21 '14 at 17:30
  • Aha, now that's an entirely different question. You may wish to edit it to make that clear. `HASHBYTES` does operate on binary data, so a simple `CONVERT(BINARY(8), datetime)` should do. – Jeroen Mostert Oct 21 '14 at 17:32
  • I'm doing multiple columns at once added together within a persisted computed column. That is why I was looking for the varchar value. The answer below is exactly what I needed. – mameesh Oct 21 '14 at 21:32

2 Answers2

3

"Exactly as it is" isn't really correct - something stored in the DATETIME format is actually stored as two integers. What you see when you select it is actually just those two integers, formatted in a certain way. The advantage of this is that many applications can recognize a DATETIME data type and allow you to use functions and formats against it that you wouldn't be able to apply to a character field (e.g., DATENAME, DATEADD, etc.)

To convert it to varchar in the same default format that SQL Server Management Studio will display, you can use format code 121, like this:

SELECT CONVERT(VARCHAR(30), GETDATE(),121)

This will return 2014-10-21 13:24:31.353, which is the same format that SSMS displays by default.

Community
  • 1
  • 1
AHiggins
  • 7,029
  • 6
  • 36
  • 54
0

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

Refer page link

Community
  • 1
  • 1