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

Converting datatype on column...

What impact will changing the datatype of a column from
smalldatetime to datetime? Currently, I need to allow for record insertion
where the date may exeed year 2079.Eric wrote:
> What impact will changing the datatype of a column from
> smalldatetime to datetime? Currently, I need to allow for record
> insertion where the date may exeed year 2079.
That should be fine. The column will require twice the number of bytes
of storage in order to support datetime (8 bytes total). Newly inserted
values will show additional precision unless you account for this.
create table ABC (MyDate smalldatetime)
insert into dbo.ABC values (getdate())
insert into dbo.ABC values ('2005-01-10T14:22:22')
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00
2005-01-10 14:22:00
Alter Table dbo.ABC
ALTER COLUMN MyDate DATETIME
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00.000
2005-01-10 14:22:00.000
insert into dbo.ABC values (getdate())
insert into dbo.ABC values ('2005-01-10T14:22:22')
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00.000
2005-01-10 14:22:00.000
2005-09-01 17:46:57.827
2005-01-10 14:22:22.000
drop table ABC
David Gugick
Quest Software
www.imceda.com
www.quest.com

converting boolean to bit

I have a control with checkboxes. The checkbox.checked property returns a boolean value. If I want to insert a record into a table that has a corresponding column of type bit, how do I do it?

I tried

CType(myChkBox.checked,String)

, which returns the strings "True" or "False". But, I get the error

The name 'True' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

I then tried

"CAST(" & CType(myChkBox.checked,String) & ",bit)"

, but got the same error.

I can write a function that will return a 1 or a 0 but that seems ludicrous. Surely there must be a more elegant way to use the result of a checkbox in an SQL statement.

Thanks
Martindoes this work ?

IIf(CheckBox1.Checked = True, 1, 0)

|||There is, you should be using parameters not on-the-fly sql strings.|||pkr, I agree that I should be using parameters, and I will get around to it before I finalise this control. I am not in front of my code at the moment, do you think that

dim prmChecked as new SqlParameter("@.myParam",SqlDbType.Bit)
prmChecked = myChkBox.checked

will work? According to the doco, the SqlDataType Bit takes an unsigned integer of 1 or 0. So, I would still have the same problem.

ndinaker, thanks for your suggestion. Is this the way its usually done?

Martin|||The checked flag is a boolean. Even *if* it doesn't automatically convert boolean to bit, it's trivial to convert boolean to int.

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 returned value to Integer

I have the following stored procedure for creating a transaction record and after inserting the record, the transaction id is returned.

----------------
DECLARE @.TransactionID int
<-- INSERT statement (after inserting the record, select the identity ID) --->
Select @.TransactionID = @.@.Identity
RETURN
----------------

.
.
.
Dim transactionID As Integer
connection.Open()
Dim reader As SqlDataReader
reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim p3 As New SqlParameter("@.TransactionID", SqlDbType.Int)
p3.Direction = ParameterDirection.Output
transactionID = Convert.ToInt16(p3.Value)
connection.Close()
.
.
.

I wanna retrieve the transactionID of the newly inserted record so that I can use it in my next step of inserting a new record to another table which has reference to the transactionID as a foreign key. However, I encountered error and suspect that it is due to the conversion of the output to Integer as it worked when I tested using dummy Integers.

I tried many different ways of conversion but couldn't resolve the error. Could anyone help?

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

|||

ndinakar:

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

hmm.. pardon me as this is my first time with asp.net

In the article:

myCommand.Parameters.Add(New SqlParameter("@.ProductId",SqlDbType.Int))

myCommand.Parameters.Direction = ParameterDirection.Output

The line in red doesn't work. I guess I'm using asp.net 2.0, that's why... so I changed to something like

command.Parameters("@.ProductID").Direction = ParameterDirection.Output

Yupz.. I'm retrieving through output parameters, I tried ExecuteReader but still couldn't work.

Actually I have these two tables for a shopping cart project - OrderTransaction and OrderDetails

OrderTransaction stores general information of TransactionID, Status, ShippingAddress etc of an order. OrderDetails stores details of a particular transaction which has foreign keys references to the Ids of OrderTransaction, Customers and Products table.
Hence, the reason for retrieving the TransactionID is so that I can create records for the items in the shopping cart in the OrderDetails for the particular transaction.

Code:

Dim transactionID As Integer
Dim connection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
Dim command As SqlCommand = New SqlCommand("AddOrders", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@.CustomerID", Context.Session("myID"))
command.Parameters.AddWithValue("@.DateCreated", DateTime.Now.ToString())
command.Parameters.AddWithValue("@.Verified", 0)
command.Parameters.AddWithValue("@.Cancelled", 0)
command.Parameters.AddWithValue("@.CustomerName", nameTB.Text)
command.Parameters.AddWithValue("@.ShippingAddress", shippingaddressTB.Text)
command.Parameters.AddWithValue("@.CustomerEmail", emailTB.Text)
command.Parameters.Add("@.TransactionID", SqlDbType.SmallInt)
command.Parameters("@.TransactionID").Direction = ParameterDirection.Output
connection.Open()
command.ExecuteNonQuery()
transactionID = Convert.ToInt16(command.Parameters("@.TransactionID").Value)
connection.Close()

Stored Procedure:

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50)
)

AS

DECLARE @.TransactionID int

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated), @.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)

Select @.TransactionID = @.@.Identity

RETURN

The error is
"Procedure or function AddOrders has too many arguments specified."
Line 161: command.ExecuteNonQuery()

I think that's because I did something wrong in the lines ingreen.Appreciate if anyone helps. Thanks in advanced.
|||(1) The error is exactly what it says. You are trying to add @.TransactionId as a parameter when you havent declared it as a parameter in your stored proc. modify your stored proc as follows:
(2) SCOPE_IDENTITY() is more efficient than @.@.IDENTITY. Check out books on line for more explanation.
(3) Since the value you are returning is through OUTPUT parameters, use ExecuteNonQuery.

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50),
@.TransactionId INT OUTPUT
)

AS

BEGIN
SET NOCOUNT ON

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated),@.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)
SELECT @.TransactionID= SCOPE_IDENTITY()

SET NOCOUNT OFF
END

Convert Datetime of query otimize.

Hello All,

In following statement in SQL, Which one should I use and why. My intention to get the record between the date and with style 101.
Which is in following is right one. If I use first('A') then it's little bit slower than second 'B'. So Please suggest me asap.

A.

convert(datetime,convert (nvarchar,Cert_WarehouseDetails.IssuedDateX,101)) <= '3/29/2004')
and
(Cert_WarehouseDetails.IssuedDateX is NOT NULL AND
convert(datetime,Cert_WarehouseDetails.IssuedDateX ,101) <= '3/11/2004')
convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/12/2004')

B
Instead that Can I use like below, as

(convert(datetime,Cert_WarehouseDetails.IssuedDate X,101) >= '1/1/2004') AND
(Cert_WarehouseDetails.IssuedDateX is NOT NULL AND
convert(datetime,Cert_WarehouseDetails.IssuedDateX ,101) <= '3/11/2004')

Please reply to me asap.

Regards,
M. J.

__________________Replace all occurrances of <= and >= along with the date specified with < original_date_plus_1 and > original_date_minus_1. This way you won't have to deal with CONVERT.|||Originally posted by rdjabarov
Replace all occurrances of <= and >= along with the date specified with < original_date_plus_1 and > original_date_minus_1. This way you won't have to deal with CONVERT.

Do I have to use 'nvarchar' while converting to datetime.