Solved

date in other formats..

Posted on 2015-01-29
4
75 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 51

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

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Question has a verified solution.

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

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 …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

630 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