Solved

date in other formats..

Posted on 2015-01-29
4
66 Views
Last Modified: 2015-02-17
tsql:which are the other data types that date is allowed to be transformed into? and is there a limit on the bytes for storage?
0
Comment
Question by:25112
4 Comments
 
LVL 5

Author Comment

by:25112
ID: 40577222
for example, the first one returns void answer, but the remaining three gives same answer.. what exactly does this mean in the 'binary' example?

select cast(GETDATE() AS binary(5)) --0x2F00860F28;
 select cast(GETDATE() AS binary(6)) --0xA42F00860F28;
 select cast(GETDATE() AS binary(7)) --0x00A42F00860F28;
 select cast(GETDATE() AS binary(16)) --0x00000000000000000000A42F0086CA99;
 
 select cast(cast(GETDATE() AS binary(5)) as datetime) --'1900-02-17 08:07:45.430';
 select cast(cast(GETDATE() AS binary(6)) as datetime) --'2015-01-29 08:08:44.913';
 select cast(cast(GETDATE() AS binary(7)) as datetime) --'2015-01-29 08:08:44.913';
 select cast(cast(GETDATE() AS binary(16)) as datetime) --'2015-01-29 08:08:44.913';
 
'0xA42F00860F28' and '0x00A42F00860F28' and 0x00000000000000000000A42F00860F28 refer to same date?

other examples:

select CAST(convert(bigint,GETDATE(),110) AS bigint)
select CAST(convert(money,GETDATE(),110) AS money)
0
 
LVL 14

Assisted Solution

by:nishant joshi
nishant joshi earned 125 total points
ID: 40577308
sql server is converting value to hexa decimal not in binary.

use below link converter.

convertor
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40577410
binary(5) shows a different result because it's not large enough to keep a datetime datatype so it truncates.
binary(6) is the correct length to handle a datetime.
binary(7) or higher will result in an addition of zero to the left side (a zero for length 7, two zero for length 8 and so on).
0
 
LVL 1

Accepted Solution

by:
echobridge earned 250 total points
ID: 40577549
Take a look at this, it may help.

Conversion grid
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question