|
| |
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
 |
Date Format (SQL) - 8/25/2002 6:31:01
Hi all,
I have a Database Results with a field Called " DateSubmitted" which is displaying the date as mm/dd/yyyy. What is the SQL script that I should you in the custom query to make it display the date as dd/mm/yyyy?
The table is called " Results"
The date field is called " DateSubmitted"
Thanks
Hisham
|
|
|
|
Long Island Lune
Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline
|
RE: Date Format (SQL) - 8/25/2002 19:34:54
I had this problem too a long time ago. But I don' t know if my remedy is the best solution to this problem.
Actually I don' t think there is a solution to this in SQL and the DRW display gave me nothing but problems.
Here was my solution:
Did you create a Date/Time field in your database? That' s what I originally did. So I changed the field from Date/Time to TEXT. When I did that, my problems were solved.
But in your case, you specifically asked for mm/dd/yyyy. For me it didn' t matter. I was happy with any of the following:
January 5, 1999
Jan 5, 1999
1/5/99
1/5/1999
So when I changed it to TEXT in Access, whatever they put in came up exactly how they entered it. This was fine for me.
I made a post on this site about 3 months ago concerning the DRW and decimal points AND digits to the right of the decimal point. I could not find it.  It had some information that might have been useful for you. Anyway, The decimal points and right-side digits were not coming up in my queries. But because the numerical values I was retrieving from the database did NOT have any math applied to them (static), I was able to change them to TEXT in Access and whatever price the user typed in, came up exactly in the DRW display. So TEXT solved my problem twice.
But I know this is NOT the best way to handle this problem. Someone else will probably post with a smarter way to handle this problem. If not, it is a solution that does work.
In your case, if you did what I did, you would have to TELL your visitors HOW to enter the date (format) and hope they did it correctly.
Wait a couple of days and see if someone else posts. Bobby would probably have an answer to this problem. Or spooky or rdouglass. If you do not get better info from anybody, try my method as a last resort. It works great.
LLL
_____________________________
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Date Format (SQL) - 8/25/2002 19:36:38
Thanks long island,
I will take your advise.
Hisham
| |
|
|
|
ASPPlayground
Posts: 122
Joined: 6/25/2002
From: Toronto, Canada
Status: offline
|
RE: RE: Date Format (SQL) - 8/26/2002 0:43:02
in addition to the LCID thing Spooky mentioned, you may also want to convert all date values into medium date before sending them to SQL server.
_____________________________
Samuel Chou
ASPPlayground.NET Developer
| |
|
|
|
hhammash
Posts: 1064
Joined: 8/19/2002
Status: offline
|
RE: Date Format (SQL) - 8/26/2002 2:47:43
Hi all,
Why can' t I do it in SQL, I saw it once but I don' t remember where. For instance in a telephone field the sql was:
SELECT StaffName,Department, FORMAT (Telephone,' (###)-##-#######' ) AS NewPhone FROM staff
This displayed telephone number as (211)-11-1234567.
Best regards
Hisham
| |
|
|
|
ASPPlayground
Posts: 122
Joined: 6/25/2002
From: Toronto, Canada
Status: offline
|
RE: RE: Date Format (SQL) - 8/26/2002 12:49:35
There is no easy way in SQL server itself. The reason is simple. VBScript and SQL aren' t 100% compatible in date intepretation especially when they are installed on different machine.
You have to find a date format that both can understand and make same intepretation. And the answer is Medium Date. It looks like this:
25-Aug-2002 12:36:00
I will show you the function I use in this forum to generate Medium Date. I use Medium Date whenever I want my ASP to interact with SQL server.
function SQLNowDate()
SQLNowDate = CStr(day(now())&" -" &monthname(month(now()),true)&" -" &year(now())&" " &Time())
end function
function SQLMediumDate(str)
str = " " &str
if isDate(str) then
SQLMediumDate = CStr(day(str)&" -" &monthname(month(str),true)&" -" &year(str)&" " &timevalue(str))
else
SQLMediumDate = SQLNowDate()
end if
End function
The two functions should be used together (put into the same include)
SQLNowDate will get you Now() in Medium date format, and SQLMediumDate(str) can convert a valid date input into Medium date. If the date input is invalid then it will just return SQLNowDate.
Also, you need to specify <%@LCID=" your LCID" %> at the top of your ASP Pages.
_____________________________
Samuel Chou
ASPPlayground.NET Developer
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 