Inserting Empty Date

Oct 11, 2004

undefined

I am using VB6 and SQL7. I am asking a user to enter a date if applicable - specifically a product manufactured date. I then take that date, using the dateadd function, query a table for a specific code, and calculate an expiration date based on the particular code.

The problem - if the user does not enter a manufactured date, which is OK, SQL inserts 01/01/1900 - which I do not want. How do I handle?

I am inserting the date, if there is one, from a flexgrid. I am declaring the variable as variant. The SQL field is DateTime.

Ex:

sSQL = "Insert into RecI(manufacdate) values ('" & (flxRec.TextMatrix(ctr,14)) & "')"

Please Help!

View 2 Replies

Inserting An Empty Row

Mar 14, 2008

Hi, is there an equivalent to the mysql's " INSERT INTO table () VALUES ();"? The fields don't allow null values, and there is a primary key with auto increment. Any ideas?Thanks a lot.

View 7 Replies View Related

Bcp Is Inserting Blank Space For Empty String

Jul 23, 2005

I'm doing a bcp out of a table to a file. Some of the fields in arecord may have an empty string.When I bcp out to the file and examine it, the fields that have anempty string in the database now show up in the file as having oneblank character.Why is bcp doing this? I don't want the blank character in my output.Thanks,Eric

View 4 Replies View Related

Converting Empty String To Null When Inserting/updating

Mar 10, 2006

I am using the following query to calculate date differences:select ..........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM .....I have stored all my dates as NVARCHAR because of the issues with localization.If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table are NULL and they produce the correct result.Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.I'd like to use it as a trigger when inserting or updating to convert empty strings to NULLbefore the values are inserted.Thanks guys.

View 1 Replies View Related

Inserting Empty Values Into NOT NULL Columns Via ODBC

Jul 20, 2005

We are writing a C application that is using ODBC to insert recordsinto a database. We have a NOT NULL column that can legitimately havean empty value, i.e. we know the value and it is empty (i.e. a zerolength string).We are using SQLBindParameter() to bind a variable to theparameterized insert statement <<in the form: INSERT INTO table VALUES(?, ?, ?)>>. We are using SQLExecDirect() to process the SQL.We are running into the problem where ODBC is converts the empty (zerolength) string into a NULL value and this errors due to the fact thatthe column is defined as NOT NULL.We do not want to redefine the column as NULL, becasue myunderstanding of the correct usage of a NULL column is to indicatethat a value is unknown or meaningless... in our case we know thevalue (it is empty) and an empty value has meaning within ourapplication.I'm sure that this issue has been seen and address thousands(millions?) of times... any guidance would be appreciated.

View 2 Replies View Related

SQL Server 2012 :: Variable Declared For Inserting Records Is Setting To Empty From Second Execution Onward?

Jul 9, 2015

I am facing a strange problem in executing stored procedure. Basically my sproc will take a values from Java application and create a Insert statement. see stored procedure below.Just to give some more background- I am re writing the procedure which was written in oracle already.

Problem I am facing now is with the statement below . When I execute the procedure for first time it works fine however when I execute for second time onwards it is setting to empty. Not sure what is the problem with my declaration and setting up with values. For reference I have pasted my complete stored procedure code below.

select @L_STMT= 'INSERT INTO '+ @l_table_name + '(' + LTRIM(RTRIM((substring (@L_INS_STMT,2,len(@L_INS_STMT))))) + ') VALUES (' + LTRIM(RTRIM((substring (@L_INS_STMT1,2,len(@L_INS_STMT1))))) +')';
ALTER PROCEDURE [dbo].[PKG_OBJ_API$CREATE_OBJ]
(
@P_TYPE VARCHAR(4000),
@P_SCOPE VARCHAR(4000),
@Arrlist varchar(max),

[code]....

View 3 Replies View Related

Problem Inserting Integers And Date In A Sql Server 2005 Datatable Row And Selecting It Afterwards Based On The Date

May 4, 2007

Hi,

I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers).
I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr).
I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table.
I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds).
A where clause seems to be needed but I don’t know the syntax or don’t find the right function
I use the following code to insert the row :

command.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime, 40, "LDate"));
command.Parameters[6].Value = DateTime.Today.ToString();
command.ExecuteNonQuery();

and the following code to get the row back (to put in arraylist):

“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())�?
WHERE LDate = '" + DateTime.Today.ToString() + "'"

Which is the correct syntax? Is there a better way to insert and select based on the date?

I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging).
Today’s date is in the database but isn’t found by my tsql code I think.

Any help would be greatly appreciated!

Grtz

Pascal

View 5 Replies View Related

What Is The Best Way To Get Empty Date?

Feb 20, 2008

tbl_store
id numeric(9)
enddate datetime(8)

tbl_store Data
ID Enddate
1 01/02/2020
2
3 01/02/2010
4
5 01/02/2001

(empty enddate data is showing <null>)
This is my query...but i want to get date empty records too.
select * from tbl_store where Enddate> '02/20/2008'

I want output
1 01/02/2020
2
3 01/02/2010
4

View 21 Replies View Related

SQL Server 2012 :: Get Empty Data Set For Inserting Data?

Nov 11, 2014

I have 2 tables in my database.

one is Race table and 2nd one is Age Range.

I want to write a query where I can see all races and age range as column.

TblRace

ID, RaceName

TblAgeRange

ID,AgeRange.

There is no connection between this two table. I need to display result like below.

Race 17-20 21-30 31-40

A

B

I

W

How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.

View 1 Replies View Related

How To Update An Empty Date-type Field

May 23, 2006

Hi,
I have update function that updating sql database table.
In the table I have fields like: name, address, phone, …, install_date

updateScreen(txtSName.text, txtSLocation.text, txtSPhone.text, txtSAddress.text, txtSPostal.text, ddSCity.SelectedItem.text, ddSCountry.SelectedItem.text, txtSOwner.text, txtSInstall.text )

The function work fine except the part whent install_date field is empty then I have the following message:

System.InvalidCastException: Conversion from string "" to type 'Date' is not valid.

Ii would like to use the same update function to make an updates even when date field is empty.

If someone has any idea how make it work, I would appreciate that

Alex

View 4 Replies View Related

SSIS Converting To Date Empty Fields (no Values)

Jan 8, 2008

Hi, how do I convert dates with SSIS, when I receive dates I have no problem, with empty fields I have to go through a transformation as:

trim(field_name) == "" ? "999999" : substring....... (derive transform)

after this I have to update each field holding '999999' to NULL. (using sql task)

Is there an easier way to doing this?

Thanks

View 1 Replies View Related

Matrix Report, Date: 1900-01-01 Wrong!! Suppose To Be Empty, How?

Aug 10, 2007

Hi
I am making a report in Visual Studio. I��m making a matrix report. I have made a UNION of 2 tables. One of them has a field called Date and the other one does not. But to make a UNION of these 2 tables so that the results are printed in a Matrix I have to have the same fields�� aliases at least. So what I did is that in the second table is:
SELECT �� AS ��Date��
Right?

Now, in the report the first table gives me the dates in a format:
=Format(Fields!Estimated_Close_Date.Value, "yyyy-MM-dd")

But, the second table is it doesn��t have a date in that field when I run the report it gives me:
1900-01-01

Something I don��t want.

So, how do I make it understand that the second tables date field is suppose to be empty?

View 8 Replies View Related

Problem Inserting Date

Mar 13, 2007

Hello
           The date format in SQL Server 2000 is dd-MM-yyyy. I am writing the following code in the buttons click event. I am using a textbox and button.
            Dim conn as SqlConnection=new SqlConnection(connection string)
            Dim ins as string="insert into Sample(dval) values(' " & TextBox1.Text & " ')"
            Dim cmd as SQlCommand=new SqlCommand(sel,conn)
            conn.open()
            cmd.ExecuteNonQuerry()
            conn.close()
             When i am inserting the date 05-03-2007 in the textbox and clicking the button it is inserting date
              03-05-2007 rather than 05-03-2007. What changes should i make in the code?
Rathish

View 2 Replies View Related

Inserting Date And Time

Aug 11, 2003

I am wanting to populate a datetime field with data retrieved from 4 dropdowns month, day, year and hour 08:00 - 18:00.

I am having trouble joining them together in a format that the sql server recognises.

View 3 Replies View Related

Inserting System Date...

Jul 10, 2004

hiiii @ll..

well i want to add system date in my table... how will i do this..???

@kS

View 3 Replies View Related

Inserting Date Into String

May 5, 2008

Hi again, I am having troubles inserting a datetime value in a table to a string. what iw ant to do is have it be sent in an email. its an attendance email. here is the code i have right now:

select @summaryreport = isnull (@summaryreport + '; ',''+ char(13))+ '<BR>' +'Instructor '+instructor + ' Student ' + Fname + ' ' + Lname + ' - ' + classname +' ' + classdate from #tabledata

i get this as an error

Msg 241, Level 16, State 1, Line 92
Conversion failed when converting datetime from character string.

View 1 Replies View Related

Inserting System Date In Table

Mar 26, 2001

Hi!
I would like to insert a system date in a table when I'm inserting a row. Any help would be appreciated.

Thanks
George

View 1 Replies View Related

Inserting Just The Time Part Of The Date?

Mar 1, 2007

In the code below why does it not enter 10:00 AM but instead it enters: 1900-01-01 10:00:00.000 ?

Create Table MyDateTest
(
DateColumn datetime
)

insert into MyDateTest values ('10:00 AM')
select DateColumn from MyDateTest

--PhB

View 3 Replies View Related

Inserting The Date As Defaultvalue In A Sqldatasource Parameter

Sep 21, 2006

Hi Guys. I am trying to insert the date as the default value into the DatePosted parameter in the sqldatasource object. I have put have the following below but it doesn't work. I have also tried <asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= Date.Now %>" /> <asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= now() %>" /> I know the solution is probably simple and I look like an idiot, but excuse me because I am very knew and fragile at this lol... any help would be great :). Mike.

View 7 Replies View Related

Problem With Inserting Date In A Datetime Field

Apr 24, 2004

Hi, I have a problem when I insert a date in a datetime field in a MSSQLServer.
That's my problem:
if the server is in english version, I have to insert date with this code:

DateTime.Today.ToString("MM/dd/yyyy")

instead if the server is in italian version, I have to insert date with this code:

DateTime.Today.ToString("dd/MM/yyyy")

Is there a way to insert a date in standard way, without knowing the server version?

bye and thanks in advance

View 3 Replies View Related

Inserting NULL Values On Date Fields Trhough DAL

Jul 25, 2006

I am using a DAL and i want to insert a new row where one of the columns is DATE and it can be 'NULL'.
I am assigning SqlTypes.SqlDateTime.Null.
But when the date is saved in the database, i get the minvalue (1/01/1900) . Is there a way to put the NULL value in the database using DAL????how can i put an empty date in the database?
THANK YOU!!!

View 3 Replies View Related

SQL Statement For Inserting Date && Auto-increment Fields

Dec 8, 2006

I have a (1) date field and (2) an auto-incrementing ID field that always throw me errors when I'm doing a programmatic insert.

(1) After doing many searches on the subject, I don't think I'm using the correct syntax for the date and can't find any suggestion that works. Would appreciate your knowledge on correct SQL syntax for inserting a "today's date" field.

(2) I'm using the following code to create a new auto-incrementing ID for each record but it seems that there should be a smoother method to force the field's value to auto-increment. Any ideas?

Private objCmd As SqlCommand Private strConn As New SqlConnection(ConfigurationManager.AppSettings("conn"))...objCmd = New SqlCommand("select max(ClientID) from tblClients", strConn)Dim intClientID As Int16 = objCmd.ExecuteNonQuery + 1

View 5 Replies View Related

Inserting The Current Date And Time Into SQL Server Database

Mar 30, 2007

I need an SQL string that inserts the current date into a database.
So far I have tried:
SQL = "INSERT INTO X (START_DATE) VALUES ('" & Date.Now & "')"
mycomm = New SqlCommand(sql, myconn)
mycomm.ExecuteNonQuery()
However, there is a problem with the SQL command. The problem is related to the date. Is there a way of programatically inserting the current date/time into the SQL database? Language used is VB.

View 1 Replies View Related

Change Format Of Date Wghen Inserting Into Sql Express

Jan 7, 2008

I have a webform that has 2 calendars that i use to insert a dateFrom and dateTo into a sql database table that has the type smalldatetime. When i insert into the database i get yyyy-mm-dd hh:mm:ss but i just want the yyyy-mm-dd not the time. how can i do this with my asp.net c# code? I also has a datetime.Now() that does the same thing.1 string dateNow = DateTime.Now.ToShortDateString();
2
3 string myConnectionString = @"Data Source=SRVWEB02SQLEXPRESS;Initial Catalog=strukton_se;User ID=user;Password=secret";
4
5 SqlConnection myConnection = new SqlConnection(myConnectionString);
6 string myInsertQuery = "INSERT INTO jobs (name, description, contact, datePosted, dateFrom, dateTo) Values(@name, @description, @contact, @datePosted, @dateFrom, @dateTo)";
7 SqlCommand myCommand = new SqlCommand(myInsertQuery);
8 myCommand.Parameters.AddWithValue("name", TextBoxName.Text);
9 myCommand.Parameters.AddWithValue("description", TextBoxDescription.Text.Replace("
", "<br/>"));
10 myCommand.Parameters.AddWithValue("contact", TextBoxContact.Text.Replace("
", "<br/>"));
11 myCommand.Parameters.AddWithValue("datePosted", dateNow);
12 myCommand.Parameters.AddWithValue("dateFrom", Calendar1.SelectedDate.ToShortDateString());
13 myCommand.Parameters.AddWithValue("dateTo", Calendar2.SelectedDate.ToShortDateString());
14
15 ButtonSubmit.Enabled = false;
16
17 myCommand.Connection = myConnection;
18 myConnection.Open();
19 myCommand.ExecuteNonQuery();
20 myCommand.Connection.Close();
Thanks.

View 2 Replies View Related

T-SQL (SS2K8) :: Inserting Date Range Depending On Inputs

Mar 14, 2014

I'm trying to modify a date range, but it's much more complicated.

Let's say I have existing date ranges of:

BlockIDStartDateEndDateActive
182013-12-31 00:00:002013-12-31 00:00:001
192014-01-01 00:00:002014-01-23 00:00:001
202014-01-24 00:00:002014-01-25 00:00:001
212014-01-26 00:00:002014-02-04 00:00:001
222014-02-05 00:00:002014-02-13 00:00:001
232014-02-14 00:00:002014-02-15 00:00:001

I've created code that will do the following (depending on the inputs):

1) Create new start and end dates
2) Inactivate obsolete date ranges

View 8 Replies View Related

Problem In Inserting Date In Sql Server 7 Through Insert Query

Jul 20, 2005

hello myself avinashi am developing on application having vb 6 as front end and sql server 7as back end.when i use insert query to insert data in table then the date value ofthat query is going as 01/01/1900my query is as followsStrSql = "Insert IntoSalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"StrSql = StrSql & " Values(" & txtTransactionID.text & "," &txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &"'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),2)& "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),2)& ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),2)+ val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),0)& ")"and its output isInsert IntoSalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)in above query though i used cdate to voucherdate value still it save indatabase as 01/01/1900 though here it shows right dateplz help me its a very big issue for me & i really just fed of thisproblem

View 2 Replies View Related

Problem In Inserting A Record Whose Values Are Of Date And Time Format.

Jan 11, 2007

hello,

I am trying to insert date and time into my table.

insert into <table_name> values('12/12/2006','12:23:04');

but it displays error at " ; "

can anyone help me to figure out the problem

Thanks a lot in advance.

Regards,

Sweety

View 3 Replies View Related

Why Date Columns Default To 1900 After Inserting Dates In SQL Server Everywhere.?

Sep 19, 2006

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.
Query:

Drop table AccountReceivable
GO
--BEGIN TRANSACTION
Create table AccountReceivable
(
AccountRecID int identity (1,1) not null,
PatientID int not null,
PresentCharges int default 0 not null,
PaymentMade money default 0 not null,
PreviousBalance money default 0 not null,
BalanceDue money default 0 not null,
LastPaymentDate datetime not null,
PresentDate datetime default GetDate() not null
)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
PK_AccountRecID Primary Key (AccountRecID)
GO

ALTER TABLE AccountReceivable ADD CONSTRAINT
FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)
GO
--COMMIT
--query to find delinquent accounts
--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table
DELETE AccountReceivable
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
                    VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                            VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                            VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)
GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
                    VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

----------- -------------- ----------------------- -----------------------

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

View 6 Replies View Related

How To Use Convert Date Statement In CmdInsert.Parameters.Add(Date,SqlDbType.DateTime).Value = Date

Sep 21, 2006

HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance

View 3 Replies View Related

Inserting Data Into Two Tables (Getting ID From Table 1 And Inserting Into Table 2)

Oct 10, 2007

I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!

View 3 Replies View Related

How To Convert A Date (date/time) To A Julian Date

Jun 13, 2002

In SQL Server 2000:

How do I convert a Julian date to a Gregorian date?

How do I convert a Gregorian date to Julian?

Examples please.

Many thanks in advance.

Gary Andrews

View 2 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

ALL And Empty Set

Sep 29, 2006

SQL Server 2000 SP4. Hoping to find a logical explanation for a certainbehavior.Consider this script:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = 'CA')PRINT 'TRUE'ELSEPRINT 'FALSE'This, as expected, prints FALSE, since not all authors in CA are undercontract. Now, if the script is changed as follows:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = '')PRINT 'TRUE'ELSEPRINT 'FALSE'then the result is TRUE. In other words, the expression evaluates to TRUEwhen the select statement produces an empty set, which doesn't make senseto me. Even more interesting, the expressionNULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')still evaluates to TRUE (ANSI_NULLS is ON).Can anyone explain these results? Is this the expected behavior in the SQLstandard, or something that is specific to SQL Server? Thanks.--remove a 9 to reply by email

View 5 Replies View Related