Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old October 21st, 2004, 11:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Yes Madhu, it is HYPHEN.


_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #12 (permalink)  
Old October 21st, 2004, 07:07 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

All

I was aware yyyy-mm-dd is the usual expected format for SQL Server and mySql. I have always on insert wrapped a function around all dates forcing an American Date format (mm/dd/yyyy) Here in Australia we are strictly dd/mm/yyy when rendered on a page, people also expect this format when picking dates from a pop up calendar. This brings me to a question relating to madhukp post, what is this:

qry_set_dateformat="SET DATEFORMAT mdy"
cnn_test.execute qry_set_dateformat

Currently I have a default SQL Server 2000 instal, under no circumstances will the DB accept a dd/mm/yyyy format (it insists on either yyyy/mm/dd or mm/dd/yyyy). If I run the above piece of code will it allow the format suggested on the end of the first line?

Wind is your friend
Matt
Reply With Quote
  #13 (permalink)  
Old October 21st, 2004, 11:41 PM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

Thanks Vijay.

Regarding the fourth step I mentioned, I don't know the internals of it. But if I set it like this and then pass date values in mm/dd/yyyy format, I found that it will not cause any problem when somebody changes regional settings of server. I have tried all possible combinations in this case.

We may also set

SET DATEFORMAT ymd

if we want the date format to be yyyy-mm-dd.

Actually, I should get the current format and store it temporarily and then after running my query, restore it. But, I am not able to retrieve the current date format. GET DATEFORMAT is not working.

But, I think if we store in yyyy-mm-dd format, we do not need the above statements.
Reply With Quote
  #14 (permalink)  
Old October 22nd, 2004, 01:44 AM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

madhukp

Good day

;;;But if I set it like this and then pass date values in mm/dd/yyyy format, I found that it will not cause any problem when somebody changes regional settings of server

I have never had a problem inserting mm/dd/yyyy even if the reg settings are different, has anybody?. In addition to this, if the system regional settings are dd/mm/yyyy, this date format still will not insert.

Wind is your friend
Matt
Reply With Quote
  #15 (permalink)  
Old October 22nd, 2004, 04:55 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cochin, Kerala, India.
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

Dear Matt,

My strategies above were built not from any theoritical background. But purely on a test and see basis.

I was using mm/dd/yyyy format in a site which ran correctly for one and a half year. This means it was working on almost all dates of the previous year. But then on a day suddenly I happen to get the error.

"date time conversion resulted in error" (or something like that).

I checked the code and found there was no deviation from the above strategy. Then I posted it on a forum and somebody replied. He asked me to use the above statement (SET DATEFORMAT mdy). Then the problem got solved. So I included this also in the above strategy.

I don't know how exactly it started creating problem after one and a half years. I guess some other script (of another site hosted on same server) run some statement like SET FORMATDATE dmy, which changes the date format.

Now, I am including it in all SQL server projects (I don't want my site to have a problem after so long a time).
Reply With Quote
  #16 (permalink)  
Old October 23rd, 2004, 09:48 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:GET DATEFORMAT is not working.
Madhu,

There is nothing like GET in SQL that can help you out. LEt me see what the best one can do the retrieve the existing dateformat so that you change that to anything you want and restore it once you are done with it. Soon, will let you know on that. To be frank, I myself haven't tried to research on that, though I had such thoughts earlier. Let me use this opportunity to find that out for you.

BTW, as it says in BOL,
Syntax : SET DATEFORMAT {format | @format_var}
Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.

Remarks
This setting is used only in the interpretation of character strings as they are converted to date values. It has no effect on the display of date values.
The setting of SET DATEFORMAT is set at execute or run time and not at parse time.

It is used to interpret the values that we give while entering data. You can check that out in BOL. It doesn't affect the format while displaying the date values. Si IMO, this should not have caused any damage to the server that you have mentioned in your earlier post about the error that you faced after 1 + years. Though I am not sure what cased that.

So it is purely used to interpret the format of date value that you pass to the SQL server to be recognized as a valid date string.

Say, I don't set the dateformat to "dmy", instead I pass a value as "31/12/2004", it might result in error that you posted in your previous post. So for the safer side, it is better to set the format to dmy and then pass this value, thus you are enabling the sql server to understand that the value passed is dmy formatted date value. IMO, this shouldn't affect others date level operations on the server.

<<<Editing it one last time.>>>
Why I was suggesting to use "yyyy-mm-dd" format is because, it is default format used in sql server, so passing date in that format for sql will never result in such errors, and don't have to use SET DATEFORMAT too.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #17 (permalink)  
Old October 23rd, 2004, 07:50 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

I have my date procedures sorted however by mistake I have been reading MS information saying SQL Servers default input/output date format is mm/dd/yyy:

"By default, the date format for SQL server is in U.S. date format MM/DD/YY, unless a localized version of SQL Server has been installed."

http://support.microsoft.com/default...b;en-us;173907

What is a localized version of SQL Server? Is this selected on instal choosing a region? I know the international standard is yyyy-mm-dd and mySql's default is definatly yyyy-mm-dd. SQL Server now has me a little confused.

Wind is your friend
Matt
Reply With Quote
  #18 (permalink)  
Old October 24th, 2004, 05:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by mat41
 Currently I have a default SQL Server 2000 instal, under no circumstances will the DB accept a dd/mm/yyyy format (it insists on either yyyy/mm/dd or mm/dd/yyyy). If I run the above piece of code will it allow the format suggested on the end of the first line?
 Yes Matt, It will then allow you to use the format that is used along with SET DATEFORMAT statement.

Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #19 (permalink)  
Old October 24th, 2004, 05:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by mat41
 I have my date procedures sorted however by mistake I have been reading MS information saying SQL Servers default input/output date format is mm/dd/yyy:

"By default, the date format for SQL server is in U.S. date format MM/DD/YY, unless a localized version of SQL Server has been installed."
Not sure if that means the INPUT and OUTPUT dateformat. I would say that is for just INPUT. Default is US format, as the default language set is US english, that can be changed to any other anytime and that impacts in the default dateformat too. When the default language is set to GREEK, then DMY is its corresponding dateformat. That makes the SQL server understand that by DEFAULT any date given in dmy format is a valid dateformat. So trying to use mdy format for date values for insert/edit or assigning to variables would result in error.

Take this example.
Code:
set language greek
go

Declare @d datetime
set @d = '10/31/2004'
select @d
go
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
-- Results in Error. SET DATEFORMAT would apply here to get me out of this.

Declare @d datetime
set @d = '01/10/2004'
select @d
go
--NO ERROR resulted, shows the date value fine.
In otherwords I would rephrase my point saying, "yyyy-mm-dd is the default date storage format or OUTPUT format".
Quote:
quote:What is a localized version of SQL Server? Is this selected on instal choosing a region? I know the international standard is yyyy-mm-dd and mySql's default is definatly yyyy-mm-dd.
I believe a localised version should be the installation package that comes entirely in that language. A Japanese version of SQL server may well fit into that category.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #20 (permalink)  
Old October 24th, 2004, 06:28 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

Great post, thanking you all

Wind is your friend
Matt
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
date time format vb certified Pro VB 6 4 December 18th, 2009 08:16 AM
date format differs, need to force format somehow patricolsson ASP.NET 2.0 Basics 1 December 3rd, 2009 12:53 AM
date format differs, need to force format somehow patricolsson HTML Code Clinic 2 January 12th, 2006 05:55 AM
Format Date to time field KennethMungwira VB.NET 3 November 17th, 2003 11:48 AM
Format Date Time ganesh15 PHP How-To 2 August 20th, 2003 05:26 AM



All times are GMT -4. The time now is 05:12 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.