Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

    Talking Unanswered: Need help converting date format.

    Hi and thanx for reading my post..

    I have a reg_date field in my MSSQL DB which is formatted like this :

    dd.mm.yy tt:mm:ss (eks. 24.12.03 18:00:03)

    What i want to do is get the 8 first chars from this string so i end up with only : 24.12.03

    Have tried different variations of : convert(char(8) but not sure how i do this really..

    Have already searched the net for a solution but had to post it since i didn't find anything useful..

    Hope someone can help me out

    Best regards
    Mirador-/

  2. #2
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    select ((CONVERT(CHAR(6),getdate(),104)) + RIGHT(CONVERT(CHAR(10),getdate(),104),2))

  3. #3
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    select (CONVERT(CHAR(10),getdate(),4))

    using 104 adds the century to the year, which then has to be eliminated in the first response. Just using 4 returns only the last two digits of the year. Be carefule though, as this type of date format will be a problem in your code in 96 years ;-)
    -bpd

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Sorry, make that CHAR(8).
    -bpd

  5. #5
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

    Exclamation

    Hi and thanx for the quick reply !

    Have tried the code but still got trouble with this errormsg i get :

    --> Incorrect syntax near '.03.'

    This is the code :

    SELECT bla1, bla2, bla3, bla4 FROM TABLE WHERE blabla = 1 and blabla = 0 and CONVERT(char(8), reg_date, 4) LIKE " & strTheDate.

    ps!.. (strTheDate is asp variable..)

    I have debugged the strTheDate and i know it's the correct format : 24.12.03 ex.

    ps!.. the field is a DateTime format, just to ensure that


    Best reagards
    Mirador

  6. #6
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Can;t say I think this is the best way of doing this, but I haven't had enough coffee to do a re-write (and blindman will undercut me anyway).

    Try this (note the single quotes!):

    SELECT bla1, bla2, bla3, bla4 FROM TABLE WHERE blabla = 1 and blabla = 0 and CONVERT(char(8), reg_date, 4) = CONVERT(char(8), CAST('" & strTheDate & "' AS DATETIME),4)

    This will convert strTheDate to the same format.
    -bpd

Posting Permissions

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