473,746 Members | 2,215 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

smalldatetime convert error

1 New Member
Hi I am passing Date value form .asp form like "3/15/2007"


And i am geting error
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.



strSlq = "SELECT SUM(pieces) AS piecesTotal" _
& " FROM orders" _
& date_ordered > convert(datetim e,'" & Dateordered & "',102)"
rs.Open strSql, cn

In MS SQL Server the Date_ordered field is smalldatetime, and stores the date values like 6/15/2007.

where do i convert what, I need help....pls
Jul 10 '07 #1
2 4109
jhardman
3,406 Recognized Expert Specialist
you mean:
Expand|Select|Wrap|Line Numbers
  1. strSlq = "SELECT SUM(pieces) AS piecesTotal" _
  2. & " FROM orders" _
  3. &  " WHERE date_ordered > #" & convert(datetime, Dateordered ,102) & "#"
  4. rs.Open strSql, cn
? Let me know if this works for you.

Jared
Jul 11 '07 #2
Cintury
81 New Member
Hi I am passing Date value form .asp form like "3/15/2007"


And i am geting error
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.



strSlq = "SELECT SUM(pieces) AS piecesTotal" _
& " FROM orders" _
& date_ordered > convert(datetim e,'" & Dateordered & "',102)"
rs.Open strSql, cn

In MS SQL Server the Date_ordered field is smalldatetime, and stores the date values like 6/15/2007.

where do i convert what, I need help....pls
I'm not 100% but you may want to change that strSlq on your first statement to be the same as that rs.Open strSql...
Aug 9 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
12905
by: James Goodman | last post by:
I have an asp page which the user enters a time(e.g. 12:21). I need to insert this time into a SQL Server DB, which has a smalldatetime column. I have tried everything I can think of (such as CDate, TimeValue, FormatDateTime etc) to convert the time string, into a valid time. However, the insert always try to insert a datetime datatype (e.g. 12/12/1899 12:21:00). I am trying to process the insert through ado, eg:
2
6792
by: Lauren Quantrell | last post by:
In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004 instead of 1/31/2004 10:30:25 AM How can I do this in a stored procedure? lq
1
5573
by: Joey Martin | last post by:
How do I convert an nvarchar field to smalldatetime format? My nvarcharfield (saledatetext) is in the format YYYY-MM-DD. I want to convert is to smalldatetime (field: saledate) Thanks for the help *** Sent via Developersdex http://www.developersdex.com ***
7
32967
by: Marc Pelletier | last post by:
Hello, I have a table with a Day field, defined as smalldatetime. I am filling it from a CSharp application with the following code: DataRow r = dtStaDays.NewRow(); r= station_ID; r = sd.Date; r = rangeTide; etc.
3
3182
by: sunshinevaldes | last post by:
Hello all and thank you for your time. I have a datagrid where I want to view dates (smalldatetime) and be able to sort on the field. However, I do not want to have the time part show. If I use the convert function(convert(char,ProjDate,101) as ProjDate) the field is then a char and does not sort correctly. Any solutions? Thanks again,
7
4689
by: Bostonasian | last post by:
I have a table that contains transactional data. Such as site view by whom, when, which template, etc, etc... Everytime when I pulled the report, hh:mm:ss never matters. Only breakdown by dates, not time. Having read some chapters of Ralph Kimball's book, I am inspired to build "date" table with integer as primary key. Here's what I have for schema of transactional table. - viewed_customer_id int (4bytes)
2
2670
by: .Net Sports | last post by:
I'm using these to assign a variable to a smalldatetime object in sql server: dim todnews = DateTime.Today.ToString ( "d" ) 'connection string to server is on this line Dim strSQL2 as string ="SELECT Arrived,Title,ID,story FROM tblGeneral WHERE Arrived = 'todnews'" ...but I get a syntax error saying i can't change a string to smalldatetime data type. When I try replacing DateTime.Today.ToString (
6
7656
by: SQL Server | last post by:
I've been working this for a while. Kind of new to SQL Server functions and not seeing what I am doing wrong. I have this function CREATE FUNCTION dbo.test (@Group varchar(50)) RETURNS smalldatetime AS BEGIN Declare @retVal varchar(10) (SELECT @retVal= MIN() FROM dbo.t_master_schedules WHERE (event_id = 13) AND (group_ =@Group)) return convert(smalldatetime, @retVal, 1)
4
2432
dcharnigo
by: dcharnigo | last post by:
I am converting a varchar(10) field to a smalldatetime in a stored procedure but have run into a problem. Some of the rows contain an invalid date. varchar(10) format is mm/dd/yyyy some rows are filled with 00/00/0000 however causing the convert to fail. how can I get around this? Updating the table is not an option. My convert is as follows: ... WHERE CONVERT(datetime, CIFExpDate) < CURRENT_TIMESTAMP ORDER BY CONVERT(datetime, CIFExpDate)...
0
8970
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8795
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
9279
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9214
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6763
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6057
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4573
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2759
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2195
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.