?
Solved

converting date char values with 'hhmiss' to  datetime

Posted on 2003-02-27
8
Medium Priority
?
455 Views
Last Modified: 2012-08-13
I am in the process of rewriting a mysql script for sql server 2000 use. I have date values that are in the format 'yymmddhhmiss'. I need to convert these strings into datetime format but when including the following in the create table statement:

date as convert(datetime, convert(char(12), getdate())),

I get 'Insert Error: Column name or number of supplied values does not match table definition.'

I think I need to include a style value after getdate() but there doesn't appear to be one which fits this format. Any help would be appreciated.
0
Comment
Question by:latrinedude
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8038778
You are converting to a datetime.
Do you want a datetime or a character date as yymmddhhmiss?

The error you are getting is saying that your column name in the insert statement is incorrect or you have the wrong number of columns.

Do you have an identity?

list the columns

insert tbl (col1, col2, ...)
select .....

for the character format you have
select right(replace(replace(replace(convert(varchar(20), getdate(), 20), '-', ''), ':','') ,' ',''), 12)
0
 

Author Comment

by:latrinedude
ID: 8040836
Yes I am trying to convert to a datetime. I do not have an identity. Here is my create table statement:

CREATE TABLE items (
  cust_id int NOT NULL,
  order_id int NOT NULL default '0',
  item_id int NOT NULL default '1',
  wine_id int NOT NULL default '0',
  qty int default NULL,
  price decimal(5,2) default NULL,
  date as convert(datetime, convert(char(12), getdate())),
  PRIMARY KEY  (cust_id)
)


And an insert statement:

INSERT INTO items VALUES('1', '2', '1', '206', '3', '73.80', '021126130135');

I would like to convert strings like '021126130135' into the following format '26-11-02 13:01:35' as they are inserted into the table.
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8043671
No you don't. You want them converted to a valid datetimne format.
Datetimes are held as decimal numbers and converted to varchar for display. You need a format that can be implicitely converted to a datetime

Best format is yyyymmdd hh:mm:ss as it is unambiguous and work for all server default date formats.
You only have a 2 digit year
so

left(@d,6) + ' ' + stuff(stuff(right(@d,6),5,0,':'),3,0,':')

so the insert becomes
insert into items select .... left(@d,6) + ' ' + stuff(stuff(right(@d,6),5,0,':'),3,0,':')
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 18

Expert Comment

by:nigelrivett
ID: 8043703
Your table definition s.b.

CREATE TABLE items (
 cust_id int NOT NULL,
 order_id int NOT NULL default '0',
 item_id int NOT NULL default '1',
 wine_id int NOT NULL default '0',
 qty int default NULL,
 price decimal(5,2) default NULL,
 date as datetime,
 PRIMARY KEY  (cust_id)
)


date as convert(datetime, convert(char(12), getdate())),
is defining a computed column which will be a fixed value (the date on which you query it) and not allow entry.
That's why you get the invalid column.
0
 

Author Comment

by:latrinedude
ID: 8045380
Okay, I understand that I was wrongly trying to do an explicit conversion. I have amended the table definition but can you elaborate on this a little:

insert into items select .... left(@d,6) + ' ' + stuff(stuff(right(@d,6),5,0,':'),3,0,':')

is @d a variable I should be declaring somewhere?

If I were to take this statement:

INSERT INTO items VALUES('1', '2', '1', '206', '3', '73.80', '021126130135');

and convert it into the format you mentioned what would it the 'insert into items select' statement look like?

Sorry for all the questions, but I'm new to SQL!

0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8045747
INSERT INTO items select '1', '2', '1', '206', '3', '73.80', '021126 13:01:35'

Don't know where the insert statement comes from but best to create a stored procedure for the insert then you can pass in parameters and manipulate the data.
In this way you will have a more efficient, secure and flexible database which is easier to maintain and debug.
0
 

Author Comment

by:latrinedude
ID: 8052085
Okay, I understand that I was wrongly trying to do an explicit conversion. I have amended the table definition but can you elaborate on this a little:

insert into items select .... left(@d,6) + ' ' + stuff(stuff(right(@d,6),5,0,':'),3,0,':')

is @d a variable I should be declaring somewhere?

If I were to take this statement:

INSERT INTO items VALUES('1', '2', '1', '206', '3', '73.80', '021126130135');

and convert it into the format you mentioned what would it the 'insert into items select' statement look like?

Sorry for all the questions, but I'm new to SQL!

0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 200 total points
ID: 8056314
INSERT INTO items select '1', '2', '1', '206', '3', '73.80', '021126 13:01:35'
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question