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]

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]

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.