Solved

convertions in SQL

Posted on 2004-10-20
11
654 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
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 7

Assisted Solution

by:SQL_Stu
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)
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 25 total points
ID: 12355911
I'm wrong about converting to binary!!!  You can, as above.
0
 
LVL 26

Assisted Solution

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

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

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 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