Date Format (SQL) (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

hhammash -> 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 -> 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. [:o] 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 -> RE: Date Format (SQL) (8/25/2002 19:36:38)

Thanks long island,

I will take your advise.


Hisham



Spooky -> RE: Date Format (SQL) (8/25/2002 21:20:18)

One method would be to manipulate the LCID like so :

http://www.4guysfromrolla.com/webtech/031401-1.shtml



ASPPlayground -> 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.



hhammash -> 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 -> 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.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625