sql server formula needed

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Hi Pat!

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

Thanks,
Leesha

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

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

Reply | Quote

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 A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote