How can I insert a hyphen into an sql result data?

Factual Questions
1

I want to add a hyphen to the beginning of a date so that excel will… (ahem)… not play with it when I paste it into excel.

Problem is, sql interprets hypens as the beginnig of a string. So how do I put a hyphen IN the string?

2

I presume by hyphen you really mean apostrophe/single quote. A hyphen does not delimit the beginning of a string in SQL. The CHAR() function takes the ASCII code of a character and returns the string representation of it. If you concatenate your string to CHAR(39), you will have a single quote at the beginning of your string.

3

I say CHAR() because I recall you posting about MS SQLServer in the past – some dialects of SQL use CHR() instead, but IIRC, most use CHAR().

4

Cerowyn has already given the answer I’d have given you, but I’m curious – you want to paste a date into Excel without Excel treating it as a date? How come? (Again, just kind of curious).

Anyway, you could also set the format of the target cell(s) in Excel to “Text” before you do the paste. Then, Excel will treat whatever you drop in there as a string.

5

Well if you look in the Pit you’ll see :smiley:

I am pasting dates in the format yy/mm/dd and excel is incorrectly converting them to dd/mm/yy so 1st oct 2008 becomes 8th oct 2001

And yes I did mean apostrophe :smack:

6

Ah. I’ll peruse the Pit thread in a minute. But you can also set the date format to yy/mm/dd by selecting the cell, then the date format, and changing the Locale to “English (United Kingdom)”. Or, you could change the default country settings at the Windows level and solve the problem for good, couldn’t you?

7

Ignore me. I wasn’t paying attention to the date format you’re actually using.

8

I tried that in the past. Problem is the data is already broken when I get the oportunity to do that. So it just converst the wrong date into UK format.

So 08/10/01 (yy/mm/dd) becomes the 8th of Oct 2001. Then convert to UK and it becomes 08/01/01 (dd/mm/yy) (8th Oct 2001)

9

Anyway, this is beside the point. Is it possible, using an sql query, to insert an apostrophe in a result?

Wait. The first reply answered that.

10

An even easier way would be to add 2 additional apostrophes.

For example: select ‘’‘08/01/01’ returns '08/01/01

11

I’m confused. I think you have a SQL table which has a column of type DATE. And you want to export that as a string in a format like 'mm/dd/yy so when you inport into Excel, Excel sees it as a string, not a date number?

If that’s the goal, this is dirt-simple.

Let’s assume the SQL table column you want to export called MyDate.

Instead of SELECT MyDate, OtherColumn1, OtherColumn2 … WHERE … ORDER BY …

Just do SELECT ‘’’’ + CONVERT(nvarchar(10),MyDate) AS FoolExcelDate, OtherColumn1, OtherColumn2 … WHERE … ORDER BY …

Note that’s four single-quotes after the SELECT.

If CONVERT() doesn’t return the format you need, SQL Server also has a DATEPART() function which you can use to extract the year, month, & day separately. You can then assemble exactly what you need. e.g.

SELECT ‘’’’ + DATEPART(month, MyDate) + ‘/’ + DATEPART(day, MyDate) + ‘/’ + DATEPART(year, MyDate) AS FoolExcelDate, OtherColumn1, OtherColumn2 … WHERE … ORDER BY …

Even if you aren’t using SQL Server, your SQL engine will have some similar functions.

Or am I missing something?

And in general I agree with the others that itd be better to leave the dates as dates so Excel could perform calculations on them if desired. Resolve your formatting at the Excel import end. Goinig the leading-single-quote route is a brute force / quick-and-dirty way to make it fit, but you’re building in a problem for later.