How to convert varbinary to date

Question

Post reply

How to convert varbinary to date

ravikaliappan

SSC Veteran

Points: 276
More actions
August 9, 2017 at 5:31 am

#104481

How to convert varbinary to date with time?
I tried like this
SELECT CONVERT(datetime2, 0xB490E37742DFD408);
It doesn’t work

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99128 More actions · Answer 1

Where did that value come from initially? How was the varbinary created? Is it encrypted?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ravikaliappan SSC Veteran Points: 276 More actions · Answer 2

Thom A - Wednesday, August 9, 2017 6:33 AM
Where did that value come from initially? How was the varbinary created? Is it encrypted?

No, It is not encrypted
The orginal Value is shown below
0x0001000000FFFFFFFF0100000000000000
04
010000000
F53797374656D2E4461746554696D6502000000057469636B7308646174654461746100000910
B490E37742DFD408
B490E37742DFD488

B4

ravikaliappan SSC Veteran Points: 276 More actions · Answer 3

ravikaliappan - Wednesday, August 9, 2017 6:39 AM
Thom A - Wednesday, August 9, 2017 6:33 AM
Where did that value come from initially? How was the varbinary created? Is it encrypted?
No, It is not encrypted
The orginal Value is shown below
0x0001000000FFFFFFFF0100000000000000
04
010000000
F53797374656D2E4461746554696D6502000000057469636B7308646174654461746100000910
B490E37742DFD408
B490E37742DFD488
B4
Here B490E37742DFD408 is the date value

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

ravikaliappan - Wednesday, August 9, 2017 5:31 AM
How to convert varbinary to date with time?
I tried like this
SELECT CONVERT(datetime2, 0xB490E37742DFD408);
It doesnâ€™t work

How did you get that varbinary value? what date should it represent?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thom A SSC Guru Points: 99128 More actions · Answer 5

how was the varbinary value created? Are you sure that the original value was a datetime2? For example, if it was originally a datetime, using CONVERT(datetime2(x)) isn't going to work. You have to convert it to it's exact original form. What was the SQL that created the varbinary?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ravikaliappan SSC Veteran Points: 276 More actions · Answer 6

Luis Cazares - Wednesday, August 9, 2017 6:42 AM
ravikaliappan - Wednesday, August 9, 2017 5:31 AM
How to convert varbinary to date with time?
I tried like this
SELECT CONVERT(datetime2, 0xB490E37742DFD408);
It doesnâ€™t work
How did you get that varbinary value? what date should it represent?

We get this from log. The date is 2017-08-09 16:19:53.830

ravikaliappan SSC Veteran Points: 276 More actions · Answer 7

Thom A - Wednesday, August 9, 2017 6:44 AM
how was the varbinary value created? Are you sure that the original value was a datetime2? For example, if it was originally a datetime, using CONVERT(datetime2(x)) isn't going to work. You have to convert it to it's exact original form. What was the SQL that created the varbinary?

I tried with DATETIME still, it is not working.
It works CONVERT(datetime(x)) for without time stamp
Problem occurs only for with time stamp

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

ravikaliappan - Wednesday, August 9, 2017 6:47 AM
We get this from log. The date is 2017-08-09 16:19:53.830

Considering this
SELECT CONVERT( varbinary(max), CONVERT(datetime2, '20170809 16:19:53.830'))
Returns 0x076080DAE388253D0B

You need to identify how the varbinary was created.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thom A SSC Guru Points: 99128 More actions · Answer 9

Just had a stab in the dark and tested all the following:
SELECT CONVERT(varbinary(MAX),CONVERT(datetime2(0), '20170809 16:19:53.830')), CONVERT(varbinary(MAX),CONVERT(datetime2(5), '20170809 16:19:53.830')), CONVERT(varbinary(MAX),CONVERT(datetime, '20170809 16:19:53.830')), CONVERT(varbinary(MAX),CONVERT(datetimeoffset, '20170809 16:19:53.830')), CONVERT(varbinary(MAX),CONVERT(smalldatetime, '20170809 16:19:53.830')), CONVERT(varbinary(MAX),CONVERT(varchar(20), CONVERT(datetime2(0),'20170809 16:19:53.830'))), CONVERT(varbinary(MAX),CONVERT(varchar(20), CONVERT(datetime,'20170809 16:19:53.830')));
These returned the values:
0x00AAE500253D0B 0x05D843705E01253D0B 0x0000A7CA010D2305 0x076080DAE388253D0B0000 0xA7CA03D4 0x323031372D30382D30392031363A31393A3534 0x41756720203920323031372020343A3139504D
None of these even remotely resemble the Binary you have supplied (0xB490E37742DFD408). We really need to know the SQL or code that created that varbinary value.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ravikaliappan SSC Veteran Points: 276 More actions · Answer 10

Thom A - Wednesday, August 9, 2017 7:03 AM
Just had a stab in the dark and tested all the following:
SELECT CONVERT(varbinary(MAX),CONVERT(datetime2(0), '20170809 16:19:53.830')), CONVERT(varbinary(MAX),CONVERT(datetime2(5), '20170809 16:19:53.830')), CONVERT(varbinary(MAX),CONVERT(datetime, '20170809 16:19:53.830')), CONVERT(varbinary(MAX),CONVERT(datetimeoffset, '20170809 16:19:53.830')), CONVERT(varbinary(MAX),CONVERT(smalldatetime, '20170809 16:19:53.830')), CONVERT(varbinary(MAX),CONVERT(varchar(20), CONVERT(datetime2(0),'20170809 16:19:53.830'))), CONVERT(varbinary(MAX),CONVERT(varchar(20), CONVERT(datetime,'20170809 16:19:53.830')));
These returned the values:
0x00AAE500253D0B 0x05D843705E01253D0B 0x0000A7CA010D2305 0x076080DAE388253D0B0000 0xA7CA03D4 0x323031372D30382D30392031363A31393A3534 0x41756720203920323031372020343A3139504D
None of these even remotely resemble the Binary you have supplied (0xB490E37742DFD408). We really need to know the SQL or code that created that varbinary value.

Guid uqEventId = LOG.Trace("Test (Not Ravi date)", dtValue);
public Guid Trace(string strMessage, params object[] objects)
{
return WriteLog(LogLevel.Trace, objects, null, strMessage, null);
}

Inside WriteLog we serilize as shown below
using(MemoryStream stream = new MemoryStream())
            {
                BinaryFormatter formatter = new BinaryFormatter(SURROGATE_SELECTOR, STREAMING_CONTEXT);
                formatter.Serialize(stream, obj);

return stream.ToArray();
}

Thom A SSC Guru Points: 99128 More actions · Answer 11

ravikaliappan - Wednesday, August 9, 2017 7:12 AM
Guid uqEventId = LOG.Trace("Test (Not Ravi date)", dtValue);
public Guid Trace(string strMessage, params object[] objects)
{
            return WriteLog(LogLevel.Trace, objects, null, strMessage, null);
        }
Inside WriteLog we serilize as shown below
using(MemoryStream stream = new MemoryStream())
            {
                BinaryFormatter formatter = new BinaryFormatter(SURROGATE_SELECTOR, STREAMING_CONTEXT);
                formatter.Serialize(stream, obj);
                return stream.ToArray();
            }

I'm not a C# programmer (I do VB.net), so my knowledge is limited to "read only", however, I'm pretty sure that C# is declarative language. I can see you have a variable there called dtValue, but I can't see where you've declared the data type for that variable (I have no idea what date types are available in C#). Also, unless I'm mistaken, Guid Trace is expecting an array of object, but dtValue is being passed to it (which I would assume is not an array and is not an object but a type of date type variable, seeing as it is prefixed with dt).

I'd almost hazard a guess that because you have an array of objects, and you are passing a date type to it, it is changing to "something else"? Someone with much better C# knowledge will know far better than I. Does a variable/object with a date value map to a specific datetype in SQL? If not, I'd assume that you need to use custom code to convert it back, as the conversion to/from a binary in C# is not the same as that in SQL.

Again, this is all me guessing, as I don't know C#.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk