Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# convertions in SQL

Posted on 2004-10-20
Medium Priority
662 Views
1.how do I convert getdate() to smallint?

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

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

LVL 7

Assisted Solution

SQL_Stu earned 140 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)
0

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

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

LVL 1

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.

eg.

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

0

LVL 1

Assisted Solution

xabimond earned 100 total points
ID: 12355911
I'm wrong about converting to binary!!!  You can, as above.
0

LVL 26

Assisted Solution

Hilaire earned 180 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

CHeers

Hilaire
0

LVL 26

Expert Comment

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

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
0

LVL 26

Accepted Solution

Hilaire earned 180 total points
ID: 12356019
OK
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)
0

LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 80 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

Racimo

0

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())
0

## Featured Post

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down â€¦
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month11 days, left to enroll