Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,
    I am running query in sql server 2005 and I need to have it return the [customerID] and the [OrderDate] as a string with date in mm/dd/yyyy format but no slashes. An example of how it would look is:

    1 - 192010

    Thanks,
    Leesha

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    What is this date 192010? is that 9/1/2010?

    Are you running this from Access or an SQL view?
    If Access its straight forward. customerID & " - " & Format(OrderDate,"mdyyyy"). You may have a problem if you have to convert back to mmddyyyy.

    If in SQL you have Year, month and day functions. I am not familiar with SQL syntx though.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Pat!

    I'm running it in sql, not access. I need it for a asp.net web page.

    Thanks,
    Leesha

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You should be able to do it with the SQL Server Date functions, i.e.

    MONTH(OrderDate) + DAY(OrderDate) + YEAR(OrderDate)

    Which would return "1102010" for today.
    Wendell

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Converting to Text strings can be long winded in SQL Server
    Because it uses + to concatenate, and if this receives what it thinks
    are numbers it will ADD instead.
    because of this you often need to use Convert or cast functions to change data type.
    There is NO Access Format equivalent.

    I am sure there might be a better solution than this, but
    Suppose you have a table called tblData
    with fields ID and CDate (you need to substitute your own for these)
    then the query below gives one example that concatenates the data.
    I haven't got SQL running here so this is an off the head solution.
    There might be some easier date conversion functions.
    This is a long old formula for what ought to be simpler.

    I have left in the converted fields to compare result to

    Code:
    select    ID,
             	CDate,
             	cast(ID as nvarchar(10))
             	+ ' - ' 
             	+ 
             	case len(month(CDate)) 
               	when 1 then '0' + cast(month(CDate) as nvarchar(2)) 
               	else cast(month(CDate) as nvarchar(2)) 
             	end
             	+
                case len(day(CDate)) 
                  when 1 then '0' + cast(day(CDate) as nvarchar(2)) 
                  else cast(day(CDate) as nvarchar(2)) 
                end
                +
                 cast(year(CDate) as nvarchar(4)) AS ConvertedDate
    from  tblData
    Andrew

  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Andrew pretty much nailed it, and that's the reason that one of the most popular topics for demonstrating Extended Stored Procedures and User Defined Functions is some sort of date formatter. If you are working in a large shop, you might want to ask your DBA what kinds of extended stored procedures are installed into your installation's Master data base.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by WendellB View Post
    You should be able to do it with the SQL Server Date functions, i.e.

    MONTH(OrderDate) + DAY(OrderDate) + YEAR(OrderDate)

    Which would return "1102010" for today.
    Problem can be with this in SQL

    Month and Day do not return a 2 digit number
    Year would be 4 digits.

    Since they are numbers then SQL return for 10/01/2010 10 + 1 + 2010 = 2021
    Gets worse when you add in the ID Field as a number

    Sadly it does not have the Access & for pure Concatenation.

    Even converting the values to Text with cast or convert

    CAST(MONTH(OrderDate) AS nvarchar(2)) + CAST(DAY(OrderDate) AS nvarchar(2)) + CAST(YEAR(OrderDate) AS nvarchar(4))

    would give

    '1' + '10' + '2010' = '1102010'


    But the format needed is mmddyyyy

    It is the padding single figures with a 0 that causes the most code.
    Even using char instead of nvarchar would be no good because it would pad with trailing space rather than leading 0.

    I am happy to be corrected on any of this.
    I'll check it properly when I get to a SQL Machine.
    Andrew

  8. #8
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by AndrewKKWalker View Post
    It is the padding single figures with a 0 that causes the most code.
    Even using char instead of nvarchar would be no good because it would pad with trailing space rather than leading 0.
    Yes, indeed. The same is true with the numerous extended stored procedures and user defined functions that I've seen. BTW, the issue isn't limited to T-SQL; the same issue arises in other programming languages, too, notably C, C++, and even C#.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

Posting Permissions

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