Free Shipping on All Orders 1-866-764-1801

Vist our Online Store
+ Reply to Thread
Results 1 to 13 of 13

Thread: SQL Guru's!

  1. #1

    Member Sales Rating: (1)

    Join Date
    Jul 2003
    Location
    York, PA / Mumbai, India
    Posts
    4,474

    Default SQL Guru's!

    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.,
    If you want to go straight to SQL date, that works as well....
    There is no genuine justice in any scheme of feeding and coddling the loafer whose only ponderable energies are devoted wholly to reproduction. Nine-tenths of the rights he bellows for are really privileges and he does nothing to deserve them. We not only acquired a vast population of morons, we have inculcated all morons, old or young, with the doctrine that the decent and industrious people of the country are bound to support them for all time.-Menkin

  2. #2

    Member Sales Rating: (2)

    Join Date
    Sep 2003
    Location
    Kansas
    Posts
    7,522

    Default

    ur h4x r w3ak!

  3. #3

    Member Sales Rating: (6)

    Join Date
    Feb 2002
    Location
    Rockledge, Florida
    Posts
    11,588

    Default

    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. ;)

    Speakers
    Carver Amazing Fronts
    CS400i Center
    RT800i's Rears
    Sub Paradigm Servo 15

    Electronics
    Conrad Johnson PV-5 pre-amp
    Parasound Halo A23
    Pioneer 84TXSi AVR
    Pioneer 79Avi DVD
    Sony CX400 CD changer
    Panasonic 42-PX60U Plasma
    WMC Win7 32bit HD DVR



  4. #4

    Member Sales Rating: (1)

    Join Date
    Jul 2003
    Location
    York, PA / Mumbai, India
    Posts
    4,474

    Default

    No, this is purely for sql, no html.

    Basically, it's erroring out on trying to place the / charector into the srting.
    Last edited by jdhdiggs; 01-24-2008 at 02:04 PM.
    There is no genuine justice in any scheme of feeding and coddling the loafer whose only ponderable energies are devoted wholly to reproduction. Nine-tenths of the rights he bellows for are really privileges and he does nothing to deserve them. We not only acquired a vast population of morons, we have inculcated all morons, old or young, with the doctrine that the decent and industrious people of the country are bound to support them for all time.-Menkin

  5. #5

    Member Sales Rating: (10)

    Join Date
    Jun 2007
    Location
    Cape Cod, Massachusetts
    Posts
    1,286

    Default

    What SQL database are you using? Microsoft SQL Server? What is the field type that contains the data you're trying to manipulate?
    Speakers: Polk LSi15
    Pre: Adcom GFP-750 with HT Bypass
    Amp: Pass Labs X-150
    CD/DVD Player: Classe CDP-10
    Interconnects: MIT Shortgun S3 Pro XLR
    Speaker cables: MIT MH-750 bi-wire
    TT:Micro Seiki DD-35
    Cartridge:Denon DL-160
    Phono Pre:PS Audio GCPH

  6. #6

    Member Sales Rating: (10)

    Join Date
    Jun 2007
    Location
    Cape Cod, Massachusetts
    Posts
    1,286

    Default

    It could be several things, but have you tried changing the ampersand to a plus? Access uses & while most SQL databases use +
    Speakers: Polk LSi15
    Pre: Adcom GFP-750 with HT Bypass
    Amp: Pass Labs X-150
    CD/DVD Player: Classe CDP-10
    Interconnects: MIT Shortgun S3 Pro XLR
    Speaker cables: MIT MH-750 bi-wire
    TT:Micro Seiki DD-35
    Cartridge:Denon DL-160
    Phono Pre:PS Audio GCPH

  7. #7

    Member Sales Rating: (1)

    Join Date
    Jul 2003
    Location
    York, PA / Mumbai, India
    Posts
    4,474

    Default

    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....
    There is no genuine justice in any scheme of feeding and coddling the loafer whose only ponderable energies are devoted wholly to reproduction. Nine-tenths of the rights he bellows for are really privileges and he does nothing to deserve them. We not only acquired a vast population of morons, we have inculcated all morons, old or young, with the doctrine that the decent and industrious people of the country are bound to support them for all time.-Menkin

  8. #8

    Member Sales Rating: (0)

    Join Date
    Apr 2006
    Posts
    614

    Default

    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
    All truth passes through three stages. First, it is ridiculed, second it is violently opposed and third, it is accepted as self evident.
    Arthur Schopenhauer

  9. #9

    Member Sales Rating: (1)

    Join Date
    Jul 2003
    Location
    York, PA / Mumbai, India
    Posts
    4,474

    Default

    Quote Originally Posted by jm1 View Post
    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
    There is no genuine justice in any scheme of feeding and coddling the loafer whose only ponderable energies are devoted wholly to reproduction. Nine-tenths of the rights he bellows for are really privileges and he does nothing to deserve them. We not only acquired a vast population of morons, we have inculcated all morons, old or young, with the doctrine that the decent and industrious people of the country are bound to support them for all time.-Menkin

  10. #10

    Member Sales Rating: (29)

    Join Date
    Apr 2007
    Posts
    1,068

    Default

    SQL Server

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

    Oracle

    SELECT TO_CHAR(sysdate,'MM/DD/YYYY') FROM DUAL
    Last edited by MKZ; 01-24-2008 at 02:48 PM. Reason: Wrong Style

  11. #11

    Member Sales Rating: (6)

    Join Date
    Feb 2002
    Location
    Rockledge, Florida
    Posts
    11,588

    Default

    Quote Originally Posted by jdhdiggs View Post
    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.

    Speakers
    Carver Amazing Fronts
    CS400i Center
    RT800i's Rears
    Sub Paradigm Servo 15

    Electronics
    Conrad Johnson PV-5 pre-amp
    Parasound Halo A23
    Pioneer 84TXSi AVR
    Pioneer 79Avi DVD
    Sony CX400 CD changer
    Panasonic 42-PX60U Plasma
    WMC Win7 32bit HD DVR



  12. #12

    Member Sales Rating: (1)

    Join Date
    Jul 2003
    Location
    York, PA / Mumbai, India
    Posts
    4,474

    Default

    I was using Query analyzer if that helps. Looks like the TCMA advice works but it's still running....
    There is no genuine justice in any scheme of feeding and coddling the loafer whose only ponderable energies are devoted wholly to reproduction. Nine-tenths of the rights he bellows for are really privileges and he does nothing to deserve them. We not only acquired a vast population of morons, we have inculcated all morons, old or young, with the doctrine that the decent and industrious people of the country are bound to support them for all time.-Menkin

  13. #13

    Member Sales Rating: (29)

    Join Date
    Apr 2007
    Posts
    1,068

    Default

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

     

Similar Threads

  1. H/K AVR 635 Guru's Help!
    By Lowell_M in forum Electronics
    Replies: 11
    Last Post: 03-24-2007, 01:47 PM
  2. Any Camera Guru's Out There
    By janmike in forum The Clubhouse
    Replies: 13
    Last Post: 09-12-2006, 08:09 AM
  3. Calling all guru's !
    By noisy in forum Speakers
    Replies: 15
    Last Post: 03-18-2006, 04:11 AM
  4. Paypal Guru's...need your help
    By steveinaz in forum The Clubhouse
    Replies: 13
    Last Post: 09-17-2005, 12:21 AM
  5. TV guru's...I need your help
    By steveinaz in forum Electronics
    Replies: 20
    Last Post: 06-30-2004, 02:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts