Converting dates into floats

I have an odd database that has a date stored in a float field. The
dates originate in an external database, which is imported record-by-
record, with data cleanup and conversion, into SQL Server. We do many
of these sorts of conversions, but normally they are put into a
datetime field instead of a float.
Everything seems to work perfectly under VB. However, I am in the
process of converting from VB into SQL for a variety of reasons. This
floatdate is causing a problem, as it is always off by two days. Let
me give you an example...
On March 7 we purchased some XXX, who's expiry date is 6/5/2008. I
read in the date from the external database, which stores it as a
string: "20080605". Here is the code I use to convert it...
Function RealDate(Datestring) As Date
Dim Yr As String
Dim Mth As String
Dim Dy As String
On Error GoTo notadate
Yr = Left(Datestring, 4)
Mth = Right(Left(Datestring, 6), 2)
Dy = Right(Datestring, 2)
RealDate = Mth & "/" & Dy & "/" & Yr
Exit Function
notadate:
RealDate = 1 / 1 / 1900
End Function
So far so good. I then put that result, a VB Date, directly into the
float field in the database. If I then read that back out in VB and
cast it to a date (which is automatic if you want) I get back the same
value.
However, when I do this in SQL, I get a _slightly_ different date:
cast(price2 as datetime) as expiry
returns 2008-06-07 00:00:00.000
It's off by _two days_. At first I thought this was an epoch issue.
Looking on the 'net I see that VB uses 1/1/1970 as the epoch while SQL
Server uses 1/1/1900. Is this understanding correct? If so, how is it
that the resulting date in SQL is only off by two days, and not 70
years?
MauryMaury,
SQL Server's zero day is 1900/01/01, but I believe that (due to a mistake
somewhere along the line) that Visual Basic's zero day is 1899/12/30. (I
believe it was supposed to be 1899/12/31, so that makes two mistakes, one
for each day that your calculation is off.)
I am relying on memory since I cannot find the reference right now.
RLF
"Maury Markowitz" <maury.markowitz@.gmail.com> wrote in message
news:e8ffe61e-6b41-4b1c-b1ce-f25890d22ece@.d1g2000hsg.googlegroups.com...
>I have an odd database that has a date stored in a float field. The
> dates originate in an external database, which is imported record-by-
> record, with data cleanup and conversion, into SQL Server. We do many
> of these sorts of conversions, but normally they are put into a
> datetime field instead of a float.
> Everything seems to work perfectly under VB. However, I am in the
> process of converting from VB into SQL for a variety of reasons. This
> floatdate is causing a problem, as it is always off by two days. Let
> me give you an example...
> On March 7 we purchased some XXX, who's expiry date is 6/5/2008. I
> read in the date from the external database, which stores it as a
> string: "20080605". Here is the code I use to convert it...
> Function RealDate(Datestring) As Date
> Dim Yr As String
> Dim Mth As String
> Dim Dy As String
> On Error GoTo notadate
> Yr = Left(Datestring, 4)
> Mth = Right(Left(Datestring, 6), 2)
> Dy = Right(Datestring, 2)
> RealDate = Mth & "/" & Dy & "/" & Yr
> Exit Function
> notadate:
> RealDate = 1 / 1 / 1900
> End Function
> So far so good. I then put that result, a VB Date, directly into the
> float field in the database. If I then read that back out in VB and
> cast it to a date (which is automatic if you want) I get back the same
> value.
> However, when I do this in SQL, I get a _slightly_ different date:
> cast(price2 as datetime) as expiry
> returns 2008-06-07 00:00:00.000
> It's off by _two days_. At first I thought this was an epoch issue.
> Looking on the 'net I see that VB uses 1/1/1970 as the epoch while SQL
> Server uses 1/1/1900. Is this understanding correct? If so, how is it
> that the resulting date in SQL is only off by two days, and not 70
> years?
> Maury|||On Apr 29, 3:16=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> SQL Server's zero day is 1900/01/01, but I believe that (due to a mistake
> somewhere along the line) that Visual Basic's zero day is 1899/12/30. =A0(=I
> believe it was supposed to be 1899/12/31, so that makes two mistakes, one
> for each day that your calculation is off.)
LOL! Ok, that DOES explain it. I'll just remember to -2 from now on.
Maury

Convert varchar to datetime

Hi,
I have what seems to be a simple issue, yet I can't get it to work:
I have a table that I have imported into SQL via a .txt file , one of the
fields holds a date (which was originally in the format of '122499'). I
added, based on certain criteria a '19' or '20' in front of the year to make
it '12241999' (still in a varchar type). But now when I try to insert it
into my final table which has a datatype as datetime, I'm having
difficulties, even if I try to convert or cast to datetime, I get the error
of:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
I have read that if it is not in the default sql datetime format, this error
will appear, but I do seem to have it in the default order of mm/dd/yyyy.
Any suggestions?
Thank you in advance!Transpose the year to the beginning of the string. Instead of using CONVERT
just say:
RIGHT(col_name, 4) + LEFT(col_name, 4)
YYYYMMDD is the recommended format to guarantee. All these other format
(mmddyy, mmddyyyy, m/d/y, d/m/y, etc.) are completely and utterly for the
birds.
Please see http://www.karaszi.com/SQLServer/info_datetime.asp
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:BCAB2D7A-D6A9-4952-81C7-39114E913AE2@.microsoft.com...
> Hi,
> I have what seems to be a simple issue, yet I can't get it to work:
> I have a table that I have imported into SQL via a .txt file , one of the
> fields holds a date (which was originally in the format of '122499'). I
> added, based on certain criteria a '19' or '20' in front of the year to
> make
> it '12241999' (still in a varchar type). But now when I try to insert it
> into my final table which has a datatype as datetime, I'm having
> difficulties, even if I try to convert or cast to datetime, I get the
> error
> of:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> I have read that if it is not in the default sql datetime format, this
> error
> will appear, but I do seem to have it in the default order of mm/dd/yyyy.
> Any suggestions?
> Thank you in advance!
>|||Hi Aaron,
I tried what you suggested and it does transpose the date in my table, of
course, but I still get the same error when trying to insert that field into
my fact table which has a datetime datatype - do I still need to convert or
cast that field into datetime datatype?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Transpose the year to the beginning of the string. Instead of using CONVE
RT
> just say:
> RIGHT(col_name, 4) + LEFT(col_name, 4)
> YYYYMMDD is the recommended format to guarantee. All these other format
> (mmddyy, mmddyyyy, m/d/y, d/m/y, etc.) are completely and utterly for the
> birds.
> Please see http://www.karaszi.com/SQLServer/info_datetime.asp
>
>
> "Patrice" <Patrice@.discussions.microsoft.com> wrote in message
> news:BCAB2D7A-D6A9-4952-81C7-39114E913AE2@.microsoft.com...
>
>|||> I tried what you suggested and it does transpose the date in my table, of
> course, but I still get the same error when trying to insert that field
> into
> my fact table which has a datetime datatype - do I still need to convert
> or
> cast that field into datetime datatype?
No, but you should check if any of the data was bogus (my guess is there is
at least one) by SELECT COUNT(*) FROM OriginalTable WHERE ISDATE(Column) = 0
Just because you transpose 13131999 to 19991313 does not make it a valid
date. :-)
And thus, you see why date/time should always be stored as such, and never
as char.
A|||You are so very right. Problem solved - thank you kindly!
"Aaron Bertrand [SQL Server MVP]" wrote:

> No, but you should check if any of the data was bogus (my guess is there i
s
> at least one) by SELECT COUNT(*) FROM OriginalTable WHERE ISDATE(Column) =
0
> Just because you transpose 13131999 to 19991313 does not make it a valid
> date. :-)
> And thus, you see why date/time should always be stored as such, and never
> as char.
> A
>
>

Convert Varchar into Int

Hi

I have imported some varchar figures.

such as 0.050 (we'll call this Field)

I need to convert them into int to be used for calculations.

when I do

CAST(Field as int)

I get...

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '0.050' to a column of data type int.

Normally I dont get this error?

Anyone?

Quote:

Originally Posted by flickimp

Hi

I have imported some varchar figures.

such as 0.050 (we'll call this Field)

I need to convert them into int to be used for calculations.

when I do

CAST(Field as int)

I get...

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '0.050' to a column of data type int.

Normally I dont get this error?

Anyone?

INT datatype (SQL Server Books online (BOL)

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

therefore... cast(field as decimal(18,3))
or
cast(field as numeric(18,3))

will return

.050

INT will round to the nearest whole number.

If calculating with decimalised data, numeric data precison and scale will demand decimal or numeric datatype

cast in itself cannot explicitly cast a 0.050 'varchar' value to a whole number
therefore inserting any 0.050 value in an integer would round it down to zero (the nearest whole number)


Regards

Jim :)

Convert single record to table

Hi all!

I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like?

Thanks.

Try:

create table dbo.t3 (

pk_col int not null,

grp int not null,

Accnt int,

Assesed int,

Paid int,

constraint pk_t3 primary key (pk_col, grp) clustered

)

insert into dbo.t3(pk_col, grp, Accnt, Assesed, Paid)

select pk_col, 1 as grp, Accnt1as Accnt, Assesed1as Assesed, Paid1 as Paid

from dbo.t1

union all

select pk_col, 2 as grp, Accnt2, Assesed2, Paid2

from dbo.t1

union all

select pk_col, 3 as grp, Accnt3, Assesed3, Paid3

from dbo.t1

order by pk_col, grp

AMB

|||

Thanks! it worked great!

Convert single record to table

Hi all!

I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like?

Thanks.

Try:

create table dbo.t3 (

pk_col int not null,

grp int not null,

Accnt int,

Assesed int,

Paid int,

constraint pk_t3 primary key (pk_col, grp) clustered

)

insert into dbo.t3(pk_col, grp, Accnt, Assesed, Paid)

select pk_col, 1 as grp, Accnt1as Accnt, Assesed1as Assesed, Paid1 as Paid

from dbo.t1

union all

select pk_col, 2 as grp, Accnt2, Assesed2, Paid2

from dbo.t1

union all

select pk_col, 3 as grp, Accnt3, Assesed3, Paid3

from dbo.t1

order by pk_col, grp

AMB

|||

Thanks! it worked great!

Convert nvarchar values to integer

I have imported a text file with various data into sql table. all these values have been imported as nvarchar. I need to convert these into Integer. the format of the values is 10length i.e. 0000000.00.

example of data:
0001028.99 - needs to be shown as 1028.99
222.00 - needs to be shown as 222.00
0000190.89 - needs to be shown as 190.89
2708.99 - needs to be shown as 2708.99
00000-50.99 - needs to be shown as -50.99
-109.79 - needs to be shown as -109.70

as you can see some of the values have leading zeros and some don't.
i have tried converting from nvarchar to int and i get the error cannot convert nvarchar to int, i believe it may be because the data contains negative values as well as positive values.

Is there a split function or position function which i can use to extract the data? or any other methods which i can use would be really helpful.

Thanks

Cast the values as decimal, i.e.: (You don't want integers, that would lose the portion after the decimal.)

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyValue varchar(20)
)

INSERT INTO @.MyTable VALUES ( 0001028.99 )
INSERT INTO @.MyTable VALUES ( 222.00 )
INSERT INTO @.MyTable VALUES ( 0000190.89 )
INSERT INTO @.MyTable VALUES ( 2708.99 )
INSERT INTO @.MyTable VALUES ( 00000-50.99 )
INSERT INTO @.MyTable VALUES ( -109.79 )

SELECT MyValues = cast( MyValue AS decimal(10,2))
FROM @.MyTable

MyValues

1028.99
222.00
190.89
2708.99
-50.99
-109.79

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
--
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:
> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
> >I have imported a dbf table into mssql in a char format 19720628.
> >
> >
> > How can I convert nvarchar to datetime?
>
>|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:
> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
> > DECLARE @.str varchar(10)
> >
> > SET @.str = '19720628'
> >
> > SELECT CONVERT(datetime, @.str, 101)
> >
> >|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
> > Thanks!
> >
> > How can I convert all the rows in a column using the method?
> >
> >
> > "GlennThomas5" wrote:
> >
> > > DECLARE @.str varchar(10)
> > >
> > > SET @.str = '19720628'
> > >
> > > SELECT CONVERT(datetime, @.str, 101)
> > >
> > >|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?
Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?
|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:

> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>
>
|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)
|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:

> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>
|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
[vbcol=seagreen]
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
[vbcol=seagreen]
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:

> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>
>|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:

> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
[vbcol=seagreen]
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
>|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
[vbcol=seagreen]
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
>|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.

Convert from Varchar to datetime

I have imported a text file and one of my columns has time stamped dates in
there.
eg. 10/11/2004 09:55:28
I want to convert this to a datetime field so that I can query the field
more efficiently.
When I tried to alter the populated table I receive the warning, data may be
lost converting column x from varchar(50). So I press yes to continue and
get this error.
unable to modify table
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The conversion
of a char data type to a date time data type resulted in an out of range
datetime value.
... the statement has been terminated.
I am only new with to this stuff so I sure is relatively easy.
Thanks in advance.
TimHi
CREATE TABLE #Test
(
col DATETIME NOT NULL
)
INSERT INTO #Test VALUES ('10/11/2004 09:55:28')
GO
ALTER TABLE #Test ALTER COLUMN col DATETIME
SELECT col FROM #Test
--Or
SELECT CONVERT(DATETIME,'10/11/2004 09:55:28',120)
"Timmeah" <timoth@.optushome.com.au> wrote in message
news:42906de4$0$8119$afc38c87@.news.optusnet.com.au...
> I have imported a text file and one of my columns has time stamped dates
in
> there.
> eg. 10/11/2004 09:55:28
> I want to convert this to a datetime field so that I can query the field
> more efficiently.
> When I tried to alter the populated table I receive the warning, data may
be
> lost converting column x from varchar(50). So I press yes to continue and
> get this error.
> unable to modify table
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The conversion
> of a char data type to a date time data type resulted in an out of range
> datetime value.
> ... the statement has been terminated.
> I am only new with to this stuff so I sure is relatively easy.
> Thanks in advance.
> Tim
>|||> I am only new with to this stuff so I sure is relatively easy.
A good start is to understand how the datetime datatype work in SQL Server.
I suggest you start by
reading this:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Timmeah" <timoth@.optushome.com.au> wrote in message
news:42906de4$0$8119$afc38c87@.news.optusnet.com.au...
>I have imported a text file and one of my columns has time stamped dates in
> there.
> eg. 10/11/2004 09:55:28
> I want to convert this to a datetime field so that I can query the field
> more efficiently.
> When I tried to alter the populated table I receive the warning, data may
be
> lost converting column x from varchar(50). So I press yes to continue and
> get this error.
> unable to modify table
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The conversion
> of a char data type to a date time data type resulted in an out of range
> datetime value.
> ... the statement has been terminated.
> I am only new with to this stuff so I sure is relatively easy.
> Thanks in advance.
> Tim
>

CONVERT Float to char

We have a table that was imported from Access to SQL 2000. For some reason
the old data had phone numbers set as Float. ? If anyone can explain that
I'd love to hear it.
Anyway, I have a SQL 2000 table that has a 10 character column called
Phone_No and when I perform the INSERT I get 7.12345+009. What can I do to
just get the 10 character phone converted from Float to char?
Thank you,
AnthonyUse the STR Function STR(F, N, R) ,
Where F is any numeric value,
N is integer total number of characters you want output, and
R is integer Number of characters to right of decimal point
as example
Declare @.F Float Set @.F = 8052080080
Select @.F, Str(@.F, 10, 0)
"Anthony W DiGrigoli" wrote:

> We have a table that was imported from Access to SQL 2000. For some reason
> the old data had phone numbers set as Float. ? If anyone can explain tha
t
> I'd love to hear it.
> Anyway, I have a SQL 2000 table that has a 10 character column called
> Phone_No and when I perform the INSERT I get 7.12345+009. What can I do to
> just get the 10 character phone converted from Float to char?
> Thank you,
> Anthony