Vasi04
asked on
convertions in SQL
1.how do I convert getdate() to smallint?
convert(small, getdate()) is this correct?
2. how do I convert varchar to binary(8)?
convert(small, getdate()) is this correct?
2. how do I convert varchar to binary(8)?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Server: Msg 8115, Level 16, State 2, Line 14
Arithmetic overflow error converting expression to data type smallint.
The statement has been terminated.
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'))
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'))
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oops,
sorry xabimond, my post is redundant with yours,
I think my "refresh before post" failed due to a cache ?
sorry xabimond, my post is redundant with yours,
I think my "refresh before post" failed due to a cache ?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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),getdat
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),getdat
ASKER
Server: Msg 8115, Level 16, State 2, Line 14
Arithmetic overflow error converting expression to data type smallint.
The statement has been terminated.