SQL Guru's!

jdhdiggs

Ok, long shot I know. I usually do this formatting crap in access then dump it to sql for processing but in this case the dB is WAY too big. What I need is a SQL query for converting: 20060310 into 03/10/2006

SELECT
(Left(Right(W2S_FINAL1.[Del_creation_Dt],4),2) & "/" & Right(W2S_FINAL1.[Del_creation_Dt],2) & "/" & Left(W2S_FINAL1.[Del_creation_Dt],4)) AS OrderDate
FROM W2S_FINAL1

Keeps erroring out:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name '/'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '/'.

I know I'm missing something massively stupid., :mad:
If you want to go straight to SQL date, that works as well....

PolkThug

ur h4x r w3ak!

disneyjoe7

Ok if I understand what you're doing... Open the file up for html editor and remove any database name stuff should be like 2 lines on the first 10 lines or so. The database doesn't like to be named.

Hope that's are I'm missed what you're needed.

jdhdiggs

No, this is purely for sql, no html.

Basically, it's erroring out on trying to place the / charector into the srting.

tcrossma

What SQL database are you using? Microsoft SQL Server? What is the field type that contains the data you're trying to manipulate?

tcrossma

It could be several things, but have you tried changing the ampersand to a plus? Access uses & while most SQL databases use +

jdhdiggs

Sql server varchar field type. Haven't tried the +'s yet. Thanks!

edit: It's running but has to chew through 50MM records so it may be a while....

jm1

I don't reconize the syntax of your statement so I am not exactly sure which database you are using. I am assuming the date value is stored in the table as a character string as it looks like you are using substring commands.

Based on the error message, are strings delimited by double quotes? Is the / character a special character? Does it need to be prefixed by another character in order to be interpreted as a character in the statement?


I created the following in an Oracle10i environment:

create table temp
(
char_date VARCHAR2(8) null
);

insert into temp (char_date) values ('20060310');
insert into temp (char_date) values ('20080124');

SELECT TO_CHAR(TO_DATE(char_date,'YYYYMMDD'),'MM/DD/YYYY') AS ORDER_DATE
FROM TEMP

jdhdiggs

jm1 wrote: »

I don't reconize the syntax of your statement so I am not exactly sure which database you are using. I am assuming the date value is stored in the table as a character string as it looks like you are using substring commands.

Based on the error message, are strings delimited by double quotes? Is the / character a special character? Does it need to be prefixed by another character in order to be interpreted as a character in the statement?


I created the following in an Oracle10i environment:

create table temp
(
char_date VARCHAR2(8) null
);

insert into temp (char_date) values ('20060310');
insert into temp (char_date) values ('20080124');

SELECT TO_CHAR(TO_DATE(char_date,'YYYYMMDD'),'MM/DD/YYYY') AS ORDER_DATE
FROM TEMP

That looks a ton better. This is in a sql server environment so I'm not 100% that your syntax will work but I'll try it when I get a chance. It's still running tcrossma's version

MKZ

SQL Server

SELECT CONVERT(CHAR(10),getdate(),101)

Oracle

SELECT TO_CHAR(sysdate,'MM/DD/YYYY') FROM DUAL

disneyjoe7

jdhdiggs wrote: »

No, this is purely for sql, no html.

Basically, it's erroring out on trying to place the / charector into the srting.

I know but an web page editor.

jdhdiggs

I was using Query analyzer if that helps. Looks like the TCMA advice works but it's still running....

MKZ

You could use SELECT TOP xx to test it 1st.