Solved

date in other formats..

Posted on 2015-01-29
4
60 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 46

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now