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
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
  • 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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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.
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 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