f



SQLS7&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure

Hi,

I have a problem with updating a datetime column,
When I try to change the Column from VB I get "Incorrect syntax near
'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]

'942' is the unique key column value

However if I update any other column the syntax is fine

The same blanket update query makes the changes no matter what is
updated

The problem only happens when I set a unique key on the date field in
question
Key is a composite of an ID, and 2 date fields

If I allow duplicates in the index it all works perfectly

I am trying to trap 'Duplicate value in index' (which is working on
other non-date columns in other tables)

This is driving me nuts

Any help would be appreciated
0
sp2044 (3)
12/11/2003 2:52:20 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

5 Replies
316 Views

Similar Articles

[PageSpeed] 33

"S.Patten" <sp2044@hotmail.com> wrote in message
news:56a34872.0312110652.1a530358@posting.google.com...
> Hi,
>
> I have a problem with updating a datetime column,
> When I try to change the Column from VB I get "Incorrect syntax near
> '942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]
>
> '942' is the unique key column value
>
> However if I update any other column the syntax is fine
>
> The same blanket update query makes the changes no matter what is
> updated
>
> The problem only happens when I set a unique key on the date field in
> question
> Key is a composite of an ID, and 2 date fields
>
> If I allow duplicates in the index it all works perfectly
>
> I am trying to trap 'Duplicate value in index' (which is working on
> other non-date columns in other tables)
>
> This is driving me nuts
>
> Any help would be appreciated

It's not possible to say for sure without seeing the UPDATE statement, but
it may be that you're building the UPDATE statement in VB and you're not
quoting a new column value correctly. You should probably print out the SQL
string you're sending to the server to check that it's what you expect, and
try to execute it directly in Query Analyzer.

However, that's just a guess - if you can post the DDL for your table, as
well as the UPDATE statement, it will be easier for someone to help you.

Simon


0
sql (2120)
12/11/2003 6:27:38 PM
> It's not possible to say for sure without seeing the UPDATE statement, but
> it may be that you're building the UPDATE statement in VB and you're not
> quoting a new column value correctly. You should probably print out the SQL
> string you're sending to the server to check that it's what you expect, and
> try to execute it directly in Query Analyzer.
> 
> However, that's just a guess - if you can post the DDL for your table, as
> well as the UPDATE statement, it will be easier for someone to help you.
> 
> Simon

I have found out how to fix my problem - but don't understand the
reason the original version works when a non-unique index field is
being undated

from vb this SP call works if unique index (on first 3 fields) is made
non-unique
**** this is a test update that should cause a Duplicate error ****

dbo.up_parmupd_Holiday
('942','02/08/03','02/08/03','35','24','7','16','Early morning
pickup','2 days by coach','79.5','0','0','9','15','11','8.5','7.5','1','9','0','0','N')

however if I make the index unique and re-rerun then I get the ADO
error (NativeError=170):
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near '942'.

but it works correctly giving "Duplicate value in index" if I remove
the brackets () from the statement

This is strange because until I added the unique to the index it all
worked perfectly

I wouldn't have thought the table/index structures should matter to
the syntax of the stored procedure call in regard to whether brackets
are used or not

This app has hundreds of SP calls and they are all bracket'ed without
issue - until this one

I guess I should go on a bracket hunt

Here are the TABLE/SP definitions

CREATE TABLE [dbo].[Holiday_T] (
	[Holiday_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Destination_ID] [int] NOT NULL ,
	[Departure_Date_dt] [datetime] NOT NULL ,
	[Return_Date_dt] [datetime] NOT NULL ,
	[Category_ID] [tinyint] NULL ,
	[Tour_Op_ID] [int] NULL ,
	[Arrangement_ID] [int] NULL ,
	[Country_ID] [int] NULL ,
	[Comment_vc] [varchar] (100) NULL ,
	[Information_vc] [varchar] (500) NULL ,
	[Adult_Cost_sm] [smallmoney] NULL ,
	[Child_Cost_sm] [smallmoney] NULL ,
	[Discount_Cost_sm] [smallmoney] NULL ,
	[Single_Supplement_sm] [smallmoney] NULL ,
	[Deposit_sm] [smallmoney] NULL ,
	[Commission_sm] [smallmoney] NULL ,
	[Tour_Op_Insurance_sm] [smallmoney] NULL ,
	[MIS_Insurance_sm] [smallmoney] NULL ,
	[Room1_si] [smallint] NULL ,
	[Room2_si] [smallint] NULL ,
	[Room3_si] [smallint] NULL ,
	[Room4_si] [smallint] NULL ,
	[Cancelled_ch] [char] (1) NULL ,
	[Current_bt] [bit] NULL 
) ON [PRIMARY]
GO

Unique index on [Holiday_ID]
Index on [Departure_Date_dt]
Unique index on [Destination_ID][Departure_Date_dt][Return_Date_dt]

CREATE PROCEDURE up_parmupd_Holiday
	(@Holiday_ID int,
	@Departure_Date datetime,
	@Return_Date datetime,
	@Category_ID int, @Tour_Operator_ID int, @Arrangement_ID int,
@Country_ID int,
	@Comment VarChar(100), @Information VarChar(500),
	@Adult_Cost Real, @Child_Cost Real, @Discount_Cost Real,
	@Single_Supplement Real, @Deposit Real, @Commission Real,
	@Tour_Op_Insurance Real, @MIS_Insurance Real, 
	@Room1 int, @Room2 int, @Room3 int, @Room4 int,
	@Cancelled Char(1)) AS
UPDATE Holiday_T
SET 	Departure_Date_dt = @Departure_Date,
    	Return_Date_dt = @Return_Date,
    	Category_ID = @Category_ID,
    	Tour_Op_ID = @Tour_Operator_ID,
    	Arrangement_ID = @Arrangement_ID,
	Country_ID = @Country_ID,
    	Comment_vc = @Comment,
    	Information_vc = @Information,
    	Adult_Cost_sm = @Adult_Cost,
    	Child_Cost_sm = @Child_Cost,
    	Discount_Cost_sm = @Discount_Cost,
    	Single_Supplement_sm = @Single_Supplement,
    	Deposit_sm = @Deposit,
    	Commission_sm = @Commission,
    	Tour_Op_Insurance_sm = @Tour_Op_Insurance,
    	MIS_Insurance_sm = @MIS_Insurance,
    	Room1_si = @Room1,
    	Room2_si = @Room2,
    	Room3_si = @Room3,
    	Room4_si = @Room4,
    	Cancelled_ch = @Cancelled
WHERE Holiday_ID = @Holiday_ID
GO

Thanks
0
sp2044 (3)
12/12/2003 12:25:58 PM
S.Patten (sp2044@hotmail.com) writes:
> from vb this SP call works if unique index (on first 3 fields) is made
> non-unique
> **** this is a test update that should cause a Duplicate error ****
> 
> dbo.up_parmupd_Holiday
> ('942','02/08/03','02/08/03','35','24','7','16','Early morning
> pickup','2 days by 
> coach','79.5','0','0','9','15','11','8.5','7.5','1','9','0','0','N')
>
> however if I make the index unique and re-rerun then I get the ADO
> error (NativeError=170):
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> syntax near '942'.
> 
> but it works correctly giving "Duplicate value in index" if I remove
> the brackets () from the statement

It is not wholly clear how you pass the above to SQL Server, but it
seems as you are simply passing it as an SQL statement with 
adCmdText. In such case the error message is obvious, because the
above is not syntactially correct T-SQL. You don't have parentheses
around parameter lists when you can stored procedures.

The uniqueness of the index does not matter. I would guess that you were
changing something else at the same time.
 

-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
0
sommar (1290)
12/12/2003 11:13:13 PM
Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns94501F4D991Yazorman@127.0.0.1>...
> S.Patten (sp2044@hotmail.com) writes:
> > from vb this SP call works if unique index (on first 3 fields) is made
> > non-unique
> > **** this is a test update that should cause a Duplicate error ****
> > 
> > dbo.up_parmupd_Holiday
> > ('942','02/08/03','02/08/03','35','24','7','16','Early morning
> > pickup','2 days by 
> > coach','79.5','0','0','9','15','11','8.5','7.5','1','9','0','0','N')
> >
> > however if I make the index unique and re-rerun then I get the ADO
> > error (NativeError=170):
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
> > syntax near '942'.
> > 
> > but it works correctly giving "Duplicate value in index" if I remove
> > the brackets () from the statement
> 
> It is not wholly clear how you pass the above to SQL Server, but it
> seems as you are simply passing it as an SQL statement with 
> adCmdText. In such case the error message is obvious, because the
> above is not syntactially correct T-SQL. You don't have parentheses
> around parameter lists when you can stored procedures.
> 
> The uniqueness of the index does not matter. I would guess that you were
> changing something else at the same time.

No (I have just re-tried this to prove it),

The same VB form with an command button works perfectly when index in
non-unique, fails when index unique - this is without closing the vb
only altering the index

This is actually not originally my application, I am maintaining it
for the future, I am not always sure whether the syntax I am passing
is ODBC (which MS give examples using brackets) or T-SQL, which is a
new variation to me, and as you say does not like brackets

I actually figured out how to fix it by trial and error with Query
Analyzer

As I said earlier, There are many examples in this app of bracketed
SQL function calls which work

THe example below WORKS PERFECTLY BRACKETS AND ALL unless I am
updating a Unique-key column (which has been expanded to include
DestinationID, DepartureDate, ReturningDate, Category, TourOperator,
Arrangement, Country)

The call is made by passing a built sql see below (the de-bracketed
version)

I am happy to use non-bracketed versions but would still like some
eplanatio to why the unique index issue exists, maybe it is versions
of other transport software (MDAC? v2.7 I believe) my PC is running

Thanks again.

UpdateHoliday(ByRef objConn As ADODB.Connection, _ (rest of header
left off)

strSQL = "dbo.up_parmupd_Holiday '" & lngHolidayID & "','" & _
strDepartureDate & "','" & strReturningDate & "','" & bytCategory &
"','" & _
intTourOperator & "','" & bytArrangement & "','" & bytCountry & "','"
& _
strComment & "','" & strInformation & "','" & curAdultCost & "','" & _
curChildCost & "','" & curDiscountCost & "','" & curSingleSupp & "','"
& _
curDeposit & "','" & curCommission & "','" & curTourOpInsurance &
"','" & _
curMISInsurance & "','" & intRooms1 & "','" & intRooms2 & "','" & _
intRooms3 & "','" & intRooms4 & "','" & strCancelled & "','" & _
strDateOverrideAllowed & "'"

'Execute the UPDATE procedure.
objConn.Execute strSQL
0
sp2044 (3)
12/15/2003 10:27:49 AM
S.Patten (sp2044@hotmail.com) writes:
> As I said earlier, There are many examples in this app of bracketed
> SQL function calls which work
> 
> THe example below WORKS PERFECTLY BRACKETS AND ALL unless I am
> updating a Unique-key column (which has been expanded to include
> DestinationID, DepartureDate, ReturningDate, Category, TourOperator,
> Arrangement, Country)
>...
> 
> strSQL = "dbo.up_parmupd_Holiday '" & lngHolidayID & "','" & _
>...
> strDateOverrideAllowed & "'"
> 
> 'Execute the UPDATE procedure.
> objConn.Execute strSQL

But there are no parentheses in this example, unless they there are 
hiding in lngHolidayID and strDateOverrideAllowed.

There may be other problems in your application when you update a 
column in a unique index. However, the syntax you showed is never
legal.

One possibility is that the error handling in this application is not fool-
proof. I recognize from the system I work. Sometimes it presents completely 
bogus errors. This is because the error message is picked up from some
global area, but the error message was passed somewhere else, and this
message is an old and tried one.



-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
0
sommar (1290)
12/15/2003 11:06:12 PM
Reply:

Similar Artilces:

date format and sorting by date
I just downloaded Eudora a few days ago and find that sorting by date didn't seem to sort by date at all. The format of the date field was time/date/zone. So, I went to options to change this to date/time/zone. That didn't seem to take effect. What am I doing wrong? FWIW, I have not paid for this, yet. I wanted to try before buying. I am quite disappointed by the above. And, it seems that the junk/spam controls are disabled in this version. What crap that is. This is really disappointing. If I cannot tell how well the features will work, why would I buy? Bill -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- In article <3fb64d2b_2@corp.newsgroups.com>, newsgroup.spam@changent.com says... > I just downloaded Eudora a few days ago and find that sorting by date didn't > seem to sort by date at all. The format of the date field was > time/date/zone. So, I went to options to change this to date/time/zone. That > didn't seem to take effect. What am I doing wrong? > > FWIW, I have not paid for this, yet. I wanted to try before buying. I am > quite disappointed by the above. And, it seems that the junk/spam controls > are disabled in this version. What crap that is. This is really > disappointing. If I cannot tell how well the features will work, why would I ...

Date in date code format
Does Access have a function to convert a date into a date code (i.e. today's date would be displayed at 051805). Bob Sanderson wrote: > Does Access have a function to convert a date into a date code (i.e. > today's date would be displayed at 051805). format(date(),"mmddyy") -- Tim http://www.ucs.mun.ca/~tmarshal/ ^o< /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me ...

how do you update a java.sql.date type in an ms access database
when i update a row in an access database the date field does not update to the correct date it brings up some funny date which i dont know where it comes from. Below is the method i use to update. maybe the date types are not compatable? thanks in advance, public static void bookIN(int custID, int rNumber,java.sql.Date bkInDate ){ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String filename = "hotelDB.mdb"; String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="; database+= filename.trim() + ";DriverID=22;READONLY=true}"; Connection con = DriverManager.getConnection( database ,"",""); int yes = 1; Statement s = con.createStatement(); s.executeUpdate("update Rooms set CustomerId ="+custID+",BookINDate ="+ bkInDate +",Occupied = "+yes+" where RoomNo = "+rNumber+" "); s.close(); con.close(); } catch (Exception err) { System.out.println("ERROR: " + err); } } "chose" wrote... > when i update a row in an access database the date field > does not update to the correct date it brings up some > funny date which i dont know where it comes from. It comes from db's attempt to parse an invalid date-string... In Access/Jet,...

converting general date format data into short date format
hello, i am doing a date comparation and i have reallize that the data i have in my database (general date format) it is causing me problems, and because i don=B4t need the time data i would like to convert all the general date data into short date format, so i would like to delete the time part of the data, it is any way i can do that ???? Thank you in advance and sorry for my english carlos=20 converting general date format data into short date format to delete the "time" portion of a date/time value, you can use an Update query, as UPDATE TableName SET TableName.DateTimeField = DateValue([TableName].[DateTimeField]); replace TableName and DateTimeField with the correct names of the table and date/time field in the table, of course. i strongly recommend that you back up your database before running the Update query; it pays to be safe with your data. to ensure that all *new* values entered in the field do not include a specific time, make sure you use the Date() function rather than the Now() function anywhere that you set the value of the field in any record. hth "savigliano" <savigliano@gmail.com> wrote in message news:1164594905.573574.229670@l12g2000cwl.googlegroups.com... hello, i am doing a date comparation and i have reallize that the data i have in my database (general date format) it is causing me problems, and because i don�t need the time data i would like to convert all the general date data into short date format, so i woul...

java reading csv file and inserting into database gives date value error.
I am using a java program to read lines from csv and insert them into mysql= database. Whenever i run my program i discovered i could not insert values= into a date field insert mysql if the value comming from the file is null.= However if i change the field to a varchar, it works fine. I want to maint= ain the field as a date field. Below is the snippet of my code. =20 queryString =3D "INSERT INTO account (MNE,DATE1,DATE2,LINK) values('"+= data[0]+"','"+data[1]+"','"+data[2]+"','"+data[3]+"')"; stmt=3Dconn.createStatement(); val =3D stmt.executeUpdate(queryString); and below is the line to be written=20 TEE,29-11-12,,90681 BBB,29-11-12,,90681 CCC,29-11-12,,90681 below is the error message (i think it comes because the value for DATE2 is= null). 712 [pool-1-thread-1] ERROR main.ProcessInboxFileTask - Failed to uplo= ad: C:\Users\Teejay\Documents\NetBeansProjects\NetBeans 6.9.1\TEEJAYD\all\G= TUS.csv com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date val= ue: '' for column 'DATE2' at row 1 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3591) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140) On Thu, 21 Mar 201...

Upgrade issues from 97 to 2003
A new Machine was set up by our IS department - it came with Office Pro 2003. Access97, Word97 and Excel 97 were added. I'm trying to update an Access 97database to 2003 but get weird compiling errors - Date, Format and Left give errors. I've registered the missing (or those ocx's) that were indicated as missing- iincluding - mscomct2.ocx - and the date picker showed up but simple things like DATE FORMAT and LEFT give compile errors - any ideas? - I've looked at Allen Browne's amazing site - his "Solving Problems with Library References" members.iinet.net.au/~allenbrowne/ser-38.html but either I couldn't figure out the right thing to do - or the solution is not here. Could there be different versions of dao360.dll that conflict?? Any ideas? help? arthur-e wrote: > A new Machine was set up by our IS department - > it came with Office Pro 2003. > Access97, Word97 and Excel 97 were added. > > I'm trying to update an Access 97database to 2003 but get weird > compiling errors - > Date, Format and Left give errors. > > I've registered the missing (or those ocx's) that were indicated as > missing- iincluding - mscomct2.ocx - and the date picker showed up but > simple things like DATE FORMAT and LEFT give compile errors > > - any ideas? - > > I've looked at Allen Browne's amazing site - his "Solving Problems > with Library References" members.iinet.net.au/~allenb...

Match date against multiple date formats
I need to match a given date against 5 different format. How can I do this ? The predefined date formats are :- Format 1 - yyyy MMM dd hh:mm:ss a Format 2 - yy-MM-dd hh:mm:ss Format 3 - MMM dd yyyy hh:mm Format 4 - dd MMM yyyy hh:mm Format 5 - dd-MM-yy hh:mm and If I get a date "2007 Jul 14 10:21:19 am", I need to pick the first option ie., Format 1. How can I go about this ? Is there any API which can determine the date format of a string? Any help is appreciated. -Aparna Here's a quick and dirty solution: SimpleDateFormat[] formatters = new SimpleDateFor...

Formatting a date to be used in a date range
Hello everyone, Okay so here is my pickle! I have a field called ARCH_DATE within a table which has a date stored like "9/1/2004 23:50:00". What I would like to do is create a date range which prompts a user to ONLY to enter a format like "mm/dd/yy". I am not interested in the time component, I really am interested in the date. I have exhuasted my resources on this one, any help would be greatyl appreciated. Tony "Tony" <anthony.hendrata@gmail.com> wrote in message news:4c37c295.0411191422.61593663@posting.google.com... > Hello everyone, > > Okay so here is my pickle! I have a field called ARCH_DATE within a > table which has a date stored like "9/1/2004 23:50:00". What I would > like to do is create a date range which prompts a user to ONLY to > enter a format like "mm/dd/yy". I am not interested in the time > component, I really am interested in the date. I have exhuasted my > resources on this one, any help would be greatyl appreciated. Dates in Access are not *stored* with any format. That is strictly a display property. If you want all values falling between two dates (inclusive) provide a form for the user to enter the two dates and have your criteria like... BETWEEN Forms!FormName!StartDate AND DateAdd("d", 1, Forms!FormName!EndDate) It would be a good idea to explicitly set these parameters in the query parameter dialog as Date DataTypes as well. Access usually assum...

Production Date/Expiry Date (Update)
"Marshall Barton" <marshbarton@wowway.com> wrote in message news:9as9lvgpnp783kogctb88c8giaepb5uf6g@4ax.com... > William Bradley wrote: > >I have two cells on a form. One of them is the "Production Date" and the >other is the "Expiry Date". The "Expiry Date" is 183 days after the >"Production Date." > >On an Excel spreadsheet, the "Expiry Date" is automatically entered, when >the "Production Date" is entered. To do this the "Expiry Date" cell carries >the following formula: "=A15+183". > >I would like to be able to do the same on an Access Form, with the results >written to the underlying table. > > I think what you're asking for can be done by using a line > of code in the Production Date text box's AfterUpdate event > procedure: > > txtExpiryDate = DateAdd("d", 183, txtProductionDate) Thank you for the above Marsh. I already have a line of code in the "After Update" of the "ProductionDate" field. Is it possible to add yours, above, as well? If so how? I tried it and it didn't work, probably due to my missing something out. Is there a way of accomplishing the same result using the "Expiry Date" field. Thank you, Bill. William Bradley wrote: > > "Marshall Barton" <marshbarton@wowway.com> wrote in message > news:9as9lvgpnp783kogctb88c8giaepb5uf...

How to convert this date to a meaningful date format
Hi all, I have a date column whose date formate is like '1061053668503'. How do I convert this date format into 'yyyy-mm-dd hh24:mi:ss' ? THANKS! ABJIANG -- Posted via http://dbforums.com On Tue, 19 Aug 2003 18:49:25 -0400, abjiang <member35506@dbforums.com> wrote: > > Hi all, > > > > > > I have a date column whose date formate is like '1061053668503'. How do I guess I'm dense or just having a bad day. Please explain exactly what IS the "format" above and what is the actual date/time into which it is transforms. Otherwise, YOYO! > I convert this date format into 'yyyy-mm-dd hh24:mi:ss' ? THANKS! > > > > ABJIANG > > > -- > Posted via http://dbforums.com > -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Look up to_date in the manual and the date formats. (sql reference) But I do not know what format that is. You are kind of shy on details. Jim "abjiang" <member35506@dbforums.com> wrote in message news:3262392.1061333365@dbforums.com... > > Hi all, > > > > > > I have a date column whose date formate is like '1061053668503'. How do > I convert this date format into 'yyyy-mm-dd hh24:mi:ss' ? THANKS! > > > > ABJIANG > > > -- > Posted via http://dbforums.com Maybe its...

Converting text dates to date format
I have a database that I imported from a csv file (I know, all the stories start out this way...) The dates were given as text strings of the form 'yyyymmdd', and were imported as text. Not all of the records have the date information, some are blank. One of these is called 'bgman' I'd like to convert those that have dates into a standard date field of the form mm/dd/yyyy. After looking around on this newsgroup and some other Access sites, here's what I did: Created a new field, 'bgmandate' with type date, format Short Date. I created an update query that 'attempts' to set the value of bgmandate with CDate(Mid([bgman],5,2) & "/" & Mid([bgman],7,2) & "/" Left([bgman],4)) Here's what happens: Nothing. All the values for bgmandate are blank. What am I doing wrong? Thanks in advance, Scott Kinney "Scott Kinney" <sakinney@ix.netcom.com> wrote in message news:BOydnZbp-Yg7CcKiU-KYuA@comcast.com... > I have a database that I imported from a csv file (I know, all the stories > start out this way...) > > The dates were given as text strings of the form 'yyyymmdd', and were > imported as text. > Not all of the records have the date information, some are blank. One of > these is called 'bgman' > > I'd like to convert those that have dates into a standard date field of the > form > mm/dd/yyyy. > > After looking around on this n...

How to convert Text dates to Date format?
After importing text date fields, the dates look like this: 12/31/2003 8:00:00 AM I'm having trouble working with these dates with Date(), Now(), etc, -- does not seem to match these dates. I've tried converting them in a query like this: SELECT CDate([ApptDate]) AS dtmApptDate FROM tblAppointments But that does not seem to help... I'm only interested in the "date" part of the date (e.g. 12/31/03) and don't need the time. Is there some simple text-to-date conversion I can perform to discard all but the date portion? Thanks in advance... How about this ... SELECT Format(CDate([ApptDate]), "mm/dd/yyyy") FROM ... or if ApptDate is already a valid date, just use the FORMAT function without the CDate() function at all -- Danny J. Lesandrini dlesandrini@hotmail.com http://amazecreations.com "deko" <dje422@hotmail.com> wrote in message news:Y%0Bb.34755$u57.10039@newssvr29.news.prodigy.com... > After importing text date fields, the dates look like this: > > 12/31/2003 8:00:00 AM > > I'm having trouble working with these dates with Date(), Now(), etc, -- > does not seem to match these dates. > I've tried converting them in a query like this: > > SELECT CDate([ApptDate]) AS dtmApptDate > FROM tblAppointments > > But that does not seem to help... > I'm only interested in the "date" part of the date (e.g. 12/31/03) and don't > need the time. Is th...

How to change char date to date format
I have source input that contains 2008/10/21 as a char var. I would like to make this a SAS date format. I have done this in the past, but for some reason, the coding technique is eluding me. DATA foo; Input date $CHAR10. ; CARDS ; 2008/10/21 2006/05/22 1996/01/15 ; ; Data foo2; set foo ; new_date = ?? ; <--- What will work here to change the char format to a true SAS date format. Proc print foo2 ; Lizette ...

ORA-01801: date format is too long for internal buffer (stored procedure called from Java using decode)
Hi, I'm calling a database procedure from Java and I sometimes get ORA-0180 from this update: UPDATE table1 SET date1=DECODE(date1,NULL,SYSDATE,date1), date2=SYSDATE WHERE ... What makes this difficult is that most of the time this works even with the very same row (it has a valid datevalue in date1). Is this due to Application Server's timezone/language changing or something like that ? I don't think I'll ever get error making the update from SqlPlus. I'll change this to NVL(date1,SYSDATE) as it should be so that might solve my problem, but I still would like to know why I get this error. Thanks in advance, Maija-Leena Maija-Leena wrote: > Hi, > > I'm calling a database procedure from Java and I sometimes get ORA-0180 from > this update: > > UPDATE table1 SET date1=DECODE(date1,NULL,SYSDATE,date1), date2=SYSDATE > WHERE ... > > What makes this difficult is that most of the time this works even with the > very same row (it has a valid datevalue in date1). > > Is this due to Application Server's timezone/language changing or something > like that ? I don't think I'll ever get error making the update from > SqlPlus. > > I'll change this to NVL(date1,SYSDATE) as it should be so that might solve > my problem, but I still would like to know why I get this error. > > Thanks in advance, > > Maija-Leena > > What type is date1 (and date2, for that matter) ...

Update automatically
1)How do I get my field, Date Last Updated, (Date/Time data type) to update automatically on my form header, after a record has been added or changed? The date format is: MM/DD/YYYY. 2)How do I get my field, ID, (AutoNumber data type) to update automatically on my form header, after a record has been added or changed? Both fields, Date Last Updated & ID, are contained in the table, Tbl Main ID. Thanks for your help! I have already made many attempts without success! John (Zufie) On Aug 11, 8:45=A0am, zufie <john.marru...@illinois.gov> wrote: > 1)How do I get my field, Date Last Updated, (Date/Time data type) to > update automatically on my form header, after a record has been added > or changed? > The date format is: MM/DD/YYYY. > > 2)How do I get my field, ID, (AutoNumber data type) to update > automatically on my form header, after a record has been added or > changed? > > Both fields, Date Last Updated & ID, are contained in the table, Tbl > Main ID. > > Thanks for your help! > > I have already made many attempts without success! > > John (Zufie) 1) put dateLastUpdated on the form (visible or not) use the form's beforeUpdate, if all the data is correct, to dateLastUpdated =3D date 2) if new record, ID will be shown when the data is saved On Aug 11, 10:16=A0am, Roger <lesperan...@natpro.com> wrote: > On Aug 11, 8:45=A0am, zufie <john.marru...@illinois.gov> wrote: > &...

Format Date in database
Hi, I need change the format of date in my database, my format is (dd-mm-yyyy) and i like (dd/mm/yyyy), thanks and sorry for my English. "Jorge Rivero" <jorgerivero@mi.cl> wrote in news:41d84151@news: > Hi, > > I need change the format of date in my database, my format is > (dd-mm-yyyy) and i like (dd/mm/yyyy), thanks and sorry for my English. DATE data type is stored in the database without ANY format. Some variation of the following should solve your problem. alter session set nls_date_format='dd/mm/yyyy' / I think mention of the to_char function which can be used to format the date value into almost any desired output format is also in order to_char(date_value.'format string') and for input to the database see to_date(:variable,'format') See the SQL manual for all the possible format options HTH -- Mark D Powell -- ...

Storing Date in SQLServer
Hi. i am facing a problem. How do i save a java.util.Date in a SQLServer column declared as a 'datetime' . and please tell me how to retrieve it too. i think that java.sql.Timestamp gives 'Abstract Method Error'. thanks.. -- Posted via http://dbforums.com "Akshat" <member36610@dbforums.com> wrote in message news:3273577.1061548549@dbforums.com... > > Hi. > > i am facing a problem. > > How do i save a java.util.Date in a SQLServer column declared as a > > 'datetime' . > > and please tell me how to retrieve it to...

Date Format Errors
Hi all, I've got an application the uses a custom date format. Users are reporting seeing errors similar to: '7/17/2005' is not a valid date and time When my program launches, I use the following code to set the custom format: DateSeparator := '-'; ShortDateFormat := 'yyyy/mm/dd'; TimeSeparator := ':'; TimeAMString := 'AM'; TimePMString := 'PM'; ShortTimeFormat := 'h:mm AMPM'; LongTimeFormat := 'h:mm:ss AMPM'; I haven't been able to reproduce the errors on my end, but I...

Date field that automatically updates record with date last modified (i.e. current date you're editing the field)
Honestly I can't believe I'm asking this question, since I've worked on Access for years, but I've been away from it for a bit and for some reason I'm feeling rusty. I need to create a date field that automatically updates with the current date and time each time a record is modified/updated. Do any of you know how to do that? Thanks "sjboyd81" <stacey.boyd@hillandknowlton.com> wrote in message news:bdaeaa.0309151337.6265fd86@posting.google.com... > Honestly I can't believe I'm asking this question, since I've worked > on Access for years, but I've been away from it for a bit and for some > reason I'm feeling rusty. > I need to create a date field that automatically updates with the > current date and time each time a record is modified/updated. Do any > of you know how to do that? > Thanks Create a form bound to your recordset. On the AfterUpdate event create a macro/code that will update the date/time field to the current date/time. eg. Private Sub frmMyForm_AfterUpdate() Me![RecordTimeStamp] = Now() End Sub Hope that helps. -- Bradley Software Developer www.hrsystems.com.au A Christian Response www.pastornet.net.au/reponse "Bradley" <bradley@REMOVETHIScomcen.com.au> wrote in message news:<M6s9b.104330$bo1.99969@news-server.bigpond.net.au>... > "sjboyd81" <stacey.boyd@hillandknowlton.com> wrote in message > ...

A date function or date issue
I have a followng variable Datetime(Character): Datetm --------------- 27MAY2008 07:50 21APR2008 12:45 18FEB2008 17:40 Now I have to break that Datetm to two varibles named date(Character) and time(Character) as below: datet time ------ ------ 27MAY2008 07:50 21APR2008 12:45 18FEB2008 17:40 Please help me. I am dieing now. > -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Joe > Matise > Sent: Thursday, April 16, 2009 12:40 PM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Re: A date function or date issue - dates are killing me > > I do hope you aren't dying as a result of a SAS problem ... you might > consider finding alternate employment if that's the case. > > You should look at the DATEPART() and TIMEPART() functions. They return > DATE and TIME independently; you can then PUT them into character values > with whichever format you need. > > -Joe > > On Thu, Apr 16, 2009 at 2:34 PM, Tom Smith <need_sas_help@yahoo.com> > wrote: > > > I have a followng variable Datetime(Character): > > > > Datetm > > --------------- > > 27MAY2008 07:50 > > 21APR2008 12:45 > > 18FEB2008 17:40 > > > > Now I have to break that Datetm to two varibles named date(Character) and > > time(Character) as below: > > > > datet time > > ------ ------ &...

How to return all the dates from a period (from a date to another date)
Thanks, JD Europe On Feb 14, 4:53 am, "JD" <jdwacc...@hotmail.com> wrote: > Thanks, > > JD > Europe where someDate between #01/01/2007# and #02/14/2007# On Feb 14, 6:53 am, "JD" <jdwacc...@hotmail.com> wrote: > Thanks, > > JD > Europe Please don't multi-post. First, create an auxiliary table, say tblIntegers, that looks like: tblIntegers ID AutoNumber theInt Long ID theInt 1 1 2 2 3 3 4 4 .... 20 20 such that the number of entries is at least equal to the number of dates in the range (inclusive). The theInt values should contain no gaps unless you want to exclude dates. Then (Note: This example uses U.S. dates but should work correctly for European dates): qryAllDatesInRange: SELECT DateAdd("d", theInt - 1, #2/2/07#) AS theDates FROM tblIntegers WHERE DateAdd("d", theInt - 1, #2/2/07#) BETWEEN #2/2/07# AND #2/5/07# ORDER BY theInt; !qryAllDatesInRange: theDates 2/2/2007 2/3/2007 2/4/2007 2/5/2007 As a parameter query: qryAllDatesInRange: PARAMETERS dtStart Date, dtEnd Date; SELECT DateAdd("d", theInt - 1, dtStart) AS theDates FROM tblIntegers WHERE DateAdd("d", theInt - 1, dtStart) BETWEEN dtStart AND dtEnd ORDER BY theInt; !qryAllDatesInRange: Enter Parameter Value dtStart 2/2/07 Enter Parameter Value dtEnd 2/5/07 theDates 2/2/2007 2/3/2007 2/4/2007 2/5/2007 James A. Fortune CDMAPoster@FortuneJames.com ...

Date & Time chooser for java 1.1
Hi Folks. Her is a couple of Java classes that can be used to select a date & time, using only the mouse. It is small, class files are about 10 kbytes, and it runs on java 1.1 ( == MSIE w/o plugin). It runs in any Locale, displaying both dates and time in the right way. http://state.dk/dt Comments are appreciated. Chris ...

Formatting more than one field at the same time (and dates as dates)
If I need to format how the content of a field is displayed, I can click ALT + ENTER from design view, and specify the format, for example, the number of decimal digits and so on. Is there a way to apply the same kind of formatting to more than one field at the same time? I tried selecting multiple fields, but if then I click ALT + ENTER I don't have the option to choose formatting. Also, how can I format dates as dates? I have a table with some dates, that are properly recognized and formatted. I create a summary query showing the average dates by certain fields. However, the average appears as a number and I can find no way to format it as a date. The only solution I could find was to export the table to Excel and format it as a date there, but it clearly is not an effective solution... Thank you in advance for your help! A1: There is no problem with selecting several controls at once, and then setting their Format property in the Properties box. A2: See: Calculated fields misinterpreted at: http://allenbrowne.com/ser-45.html The article explains how to use CVDate() around the calculated field so JET recognises it as a date. If you don't follow how to apply this, post your SQL statement. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. <myemail.an@googlemail.com> wrote in message news:1191938099.318164.262420@k79g2000hs...

Date Parameter when using Intl Date Formats
I already figured out (the hard way) I need to convert all my date parameters into USA format before executing my stored procedures where dates are used as parameters. (Format(StartDate, "m/d/yyyy hh:nn:ss AM/PM") At least I thought I did! But then I discover that if I use the following construction, the dates do not need to be formatted to USA first: Dim StartDate as Date, EndDate as Date StartDate = Date() EndDate = Date() +1 cmd.Execute , Parameters:=Array(StartDate, EndDate), Options:=adExecuteNoRecords Am I smoking dope here or did our MS friends make this more confusing than necessary? Lauren Quantrell wrote in message <1133201171.574098.20220@o13g2000cwo.googlegroups.com> : > I already figured out (the hard way) I need to convert all my date > parameters into USA format before executing my stored procedures where > dates are used as parameters. > (Format(StartDate, "m/d/yyyy hh:nn:ss AM/PM") > > At least I thought I did! > > But then I discover that if I use the following construction, the dates > do not need to be formatted to USA first: > > Dim StartDate as Date, EndDate as Date > > StartDate = Date() > EndDate = Date() +1 > > cmd.Execute , Parameters:=Array(StartDate, EndDate), > Options:=adExecuteNoRecords > > Am I smoking dope here or did our MS friends make this more confusing > than necessary? There's a bit of difference between concatenating some dynamic sql STRI...

Web resources about - SQLS7&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure - comp.databases.ms-sqlserver

Resources last updated: 3/22/2016 10:07:38 AM