Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

convertions in SQL

Posted on 2004-10-20
11
Medium Priority
?
660 Views
Last Modified: 2012-06-21
1.how do I convert getdate() to smallint?

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


2. how do I convert varchar to binary(8)?
0
Comment
Question by:Vasi04
[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
  • 3
  • 2
  • +2
11 Comments
 
LVL 7

Assisted Solution

by:SQL_Stu
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

by:Vasi04
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

by:Vasi04
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Expert Comment

by:xabimond
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

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

Assisted Solution

by:Hilaire
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

by:Hilaire
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

by:Vasi04
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

by:
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

by:Racim BOUDJAKDJI
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

by:Hilaire
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

704 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