convertions in SQL

Posted on 2004-10-20
Last Modified: 2012-06-21 do I convert getdate() to smallint?

convert(small, getdate()) is this correct?

2. how do I convert varchar to binary(8)?
Question by:Vasi04
  • 4
  • 3
  • 2
  • +2

Assisted Solution

SQL_Stu earned 35 total points
ID: 12355818
You could use the CAST or CONVERT functions:

CAST(GetDate() as smallint)
CONVERT(smallint, GetDate()

Declare @v varchar(50)

CAST(@v as binary(8)
CONVERT(binary(8), @v)

Author Comment

ID: 12355845
Error message in my QA for CAST(GetDate() as smallint)

Server: Msg 8115, Level 16, State 2, Line 14
Arithmetic overflow error converting expression to data type smallint.
The statement has been terminated.

Author Comment

ID: 12355855
In both cases, I get similar error msg

Server: Msg 8115, Level 16, State 2, Line 14
Arithmetic overflow error converting expression to data type smallint.
The statement has been terminated.

Expert Comment

ID: 12355890
These are some pretty weird conversions!

You can't convert getdate to smallint.  This is because dates are held as the nmber of days past 1/1/1900 and milliseconds past 00:00.  Converting a date to an integer will return the day partion of this.  Currently we are 38278 days past 1/1/1900.  Smallint can be used to store the values -32768 - +32768 so attempting to do the convertion will cause an overflow.  You can convert to the Integer datatype.

You can't directly convert a varchar to a binary(8).  Why do you want to?
You can, however, convert a character to its ascii number and then convert this to binary.


    select convert(binary(1), ascii('a'))


Assisted Solution

xabimond earned 25 total points
ID: 12355911
I'm wrong about converting to binary!!!  You can, as above.
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

LVL 26

Assisted Solution

Hilaire earned 45 total points
ID: 12355936
Sorry to be a pain, Vasi04, but what's the point in converting a datetime to a smallint value ?

basically the four high-weight bytes of a datetime value store the number of days since 01/01/1900
so taking the "date" part of a datetime column could be achieved as follows

select datediff(day, 0, getdate())
this returns 38278 for today
unfortunately this is too large to fit in a smallint column/variable
select cast(datediff(day, 0, getdate()) as smallint)

you could always use modulo (%) to get a smaller number,
but you'd lose part of the information.

Sorry but for the moment I don't have a clue what kind of inforamtion you expect to store in this smallint field


LVL 26

Expert Comment

ID: 12355946
sorry xabimond, my post is redundant with yours,
I think my "refresh before post" failed due to a cache ?

Author Comment

ID: 12355997
I am with you on this, Hilaire and xbimond.
I working on already exsiting db design.

I dont any clue either why this is so. I am inserting values to fields with names _chgdate and _chgtime defined as smallint. As the names indicate date and time, but  contents of this table look like  20017 and 437
LVL 26

Accepted Solution

Hilaire earned 45 total points
ID: 12356019
the root date in your foreign DB is 01/01/1950

to convert to smallint use

select cast(datediff(day, '1950.01.01', getdate()) as smallint)
LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 20 total points
ID: 12356030
To my knwledge it is not possible to do what you're asking  as you can't convert to small time
date is usually stored as yyyy-dd-mm.  If you want to convert it to int the only way to do it I know is to put it as yyyymmdd (yyyymmdd is beyond smallint capabilities) try:

to convert to yymmdd try
select cast((cast(right(cast(year(getdate()) as varchar(4)),2) as int)   * 10000) + (month(getdate()) * 100) + (day(getdate()))  as  binary)

to convert to yyyymmdd
select cast((year(getdate())   * 10000) + (month(getdate()) * 100) + (day(getdate()))  as  int)

to convert to binary yyyymmdd
select cast((year(getdate()) as int)   * 10000) + (month(getdate()) * 100) + (day(getdate()))  as  binary)

Hope this helped


LVL 26

Expert Comment

ID: 12356042
if all the values in _chgtime are in the range 0-1440,
most probalily they use a number of minutes since 00:00

to convert a date to such a number, use

select datediff(minute, convert(varchar(10),getdate(), 102), getdate())

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

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

20 Experts available now in Live!

Get 1:1 Help Now