AM/PM are saved as ?? without SQL N prefix

Question

I usually use N prefix in inserting data into sql server table when the data are not in English however when I want to save current time the AM or PM while be saved as ?? when I don't use N prefix.

This is how I save time in database here mtime is nvarchar(50):

<%
t=time
sql=insert into notes (body,mtime) values (N'" & body &"','"& t &"')"
Con.execute sql
%>

why do I need N prefix to save time? isn't output of asp time, a string in English characters?

Does this happen via SSMS? BTW, _Latin_ you mean instead of english. — Ilyes, May 23 '18 at 14:06
You desperately need to read about and start using parameterized queries. Your code is wide open to sql injection. http://bobby-tables.com/ — Sean Lange, May 23 '18 at 14:10
And why in the world is mtime nvarchar(50)? You should use the time datatype to store time information. — Sean Lange, May 23 '18 at 14:14
This code would produce a syntax error. Please post your actual code, and post the value of `t` that produces the `??` result. The problem you say you are having is not reproducible based on what you have posted. — Tab Alleman, May 23 '18 at 19:54
https://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements — Tab Alleman, May 23 '18 at 20:03

Ilyes · Answer 1 · 2018-05-23T14:34:37.270

0

I don't see why you need to store the AM/PM as STRING, all what you need is to choose the right DataType which is in your case TIME.

After you store some data, then it's easy:)

CREATE TABLE Test(
    MyTime TIME
    );

INSERT INTO Test VALUES
('08:00:00'),
('13:00:00');

SELECT CONVERT(VARCHAR(15), MyTime,100) AS TheTimeIs
FROM Test;

Results:

+-----------+
| TheTimeIs |
+-----------+
| 8:00AM    |
| 1:00PM    |
+-----------+

Bside to this it seems like you also confuse about the N' prefix, when and why we should use it, here is a Good answer about that prefix.

edited May 23 '18 at 14:34

answered May 23 '18 at 14:24

Ilyes

14,640
4
29
55

I get the time using asp TIME function and so I don't want to convert it to other formats. – Ali Sheikhpour May 23 '18 at 14:34

score 0 · Accepted Answer · answered May 24 '18 at 07:08

I found in IIS manager that the Locale of ASP was not set and so the time was being generated in Farsi language like 12:00 ق.ظ instead of 12:00 AM. I did two things and not sure if both of them was mandatory:

IIS manager > Choose server > ASP > Locale ID : 1033 (code for English US)
Changing locale of Master Database from Arabic to Latin using this instruction

AM/PM are saved as ?? without SQL N prefix

2 Answers2