|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
One Small Question regarding Date FormatI hav a very small application, which insert some date related data in sql
server. But tht application while inserting date time values. on some server gives error. because datetime format of machine/server is different. I want to make my application compatible with any date time format on the server. This can be achieved by knowing format before executing the sql statement and aftr that statement is converted to tht format to avod error. but I think this is not a optimised way. there is one universal format which works on any datetime format of any sql server. I forgeted that. can any one tell me tht so that it works on evry server. Kishor Use one of the following formats.
Year, month and day: '20050819' ISO with hours, minutes, seconds: '2005-08-19T17:59:59' ISO with hours, minutes, seconds, milliseconds: '2005-08-19T17:59:59.997' -- David Portas SQL Server MVP -- Hi,
As well as the ones David has saind I haven't had a problem with DD MMM YYYY 04 May 2005 -- Show quotekind regards Greg O Need to document your databases. Use the firs and still the best AGS SQL Scribe http://www.ag-software.com "kishor" <kis***@discussions.microsoft.com> wrote in message news:CCBC9546-5EF7-458E-9528-475B757B4A0F@microsoft.com... >I hav a very small application, which insert some date related data in sql > server. > > But tht application while inserting date time values. on some server gives > error. because datetime format of machine/server is different. I want to > make > my application compatible with any date time format on the server. > > This can be achieved by knowing format before executing the sql statement > and aftr that statement is converted to tht format to avod error. but I > think > this is not a optimised way. there is one universal format which works on > any > datetime format of any sql server. I forgeted that. can any one tell me > tht > so that it works on evry server. > > Kishor Greg,
I'm afraid that the alphabetic format is dependent on SET LANGUAGE (and a login's default language). -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "GregO" <grego@community.nospam> wrote in message news:%23H4Vt9IpFHA.1088@TK2MSFTNGP14.phx.gbl... > Hi, > As well as the ones David has saind I haven't had a problem with > DD MMM YYYY > > 04 May 2005 > > > -- > kind regards > Greg O > Need to document your databases. Use the firs and still the best AGS SQL > Scribe > http://www.ag-software.com > "kishor" <kis***@discussions.microsoft.com> wrote in message > news:CCBC9546-5EF7-458E-9528-475B757B4A0F@microsoft.com... >>I hav a very small application, which insert some date related data in sql >> server. >> >> But tht application while inserting date time values. on some server gives >> error. because datetime format of machine/server is different. I want to make >> my application compatible with any date time format on the server. >> >> This can be achieved by knowing format before executing the sql statement >> and aftr that statement is converted to tht format to avod error. but I think >> this is not a optimised way. there is one universal format which works on any >> datetime format of any sql server. I forgeted that. can any one tell me tht >> so that it works on evry server. >> >> Kishor > > Thanks Tibor
Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:uhMSnSJpFHA.2504@tk2msftngp13.phx.gbl... > Greg, > > I'm afraid that the alphabetic format is dependent on SET LANGUAGE (and a > login's default language). > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "GregO" <grego@community.nospam> wrote in message > news:%23H4Vt9IpFHA.1088@TK2MSFTNGP14.phx.gbl... >> Hi, >> As well as the ones David has saind I haven't had a problem with >> DD MMM YYYY >> >> 04 May 2005 >> >> >> -- >> kind regards >> Greg O >> Need to document your databases. Use the firs and still the best AGS SQL >> Scribe >> http://www.ag-software.com >> "kishor" <kis***@discussions.microsoft.com> wrote in message >> news:CCBC9546-5EF7-458E-9528-475B757B4A0F@microsoft.com... >>>I hav a very small application, which insert some date related data in >>>sql >>> server. >>> >>> But tht application while inserting date time values. on some server >>> gives >>> error. because datetime format of machine/server is different. I want to >>> make >>> my application compatible with any date time format on the server. >>> >>> This can be achieved by knowing format before executing the sql >>> statement >>> and aftr that statement is converted to tht format to avod error. but I >>> think >>> this is not a optimised way. there is one universal format which works >>> on any >>> datetime format of any sql server. I forgeted that. can any one tell me >>> tht >>> so that it works on evry server. >>> >>> Kishor >> >> > Hi Tibor
I had a thought about this last night. And I sure I'm on the right track here. While I understand the issue you raise I'd just point out that if you were using the French language you wouldn't enter the month in English you would do this SET LANGUAGE FRENCH SELECT ISDATE('04 août 2005') SET LANGUAGE ENGLISH SELECT ISDATE('04 May 2005') and both of these work. The problem is that maybe the input would not be in the same language as the local settings and that's when it would fail. kind regards Greg O Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:uhMSnSJpFHA.2504@tk2msftngp13.phx.gbl... > Greg, > > I'm afraid that the alphabetic format is dependent on SET LANGUAGE (and a > login's default language). > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "GregO" <grego@community.nospam> wrote in message > news:%23H4Vt9IpFHA.1088@TK2MSFTNGP14.phx.gbl... >> Hi, >> As well as the ones David has saind I haven't had a problem with >> DD MMM YYYY >> >> 04 May 2005 >> >> >> -- >> kind regards >> Greg O >> Need to document your databases. Use the firs and still the best AGS SQL >> Scribe >> http://www.ag-software.com >> "kishor" <kis***@discussions.microsoft.com> wrote in message >> news:CCBC9546-5EF7-458E-9528-475B757B4A0F@microsoft.com... >>>I hav a very small application, which insert some date related data in >>>sql >>> server. >>> >>> But tht application while inserting date time values. on some server >>> gives >>> error. because datetime format of machine/server is different. I want to >>> make >>> my application compatible with any date time format on the server. >>> >>> This can be achieved by knowing format before executing the sql >>> statement >>> and aftr that statement is converted to tht format to avod error. but I >>> think >>> this is not a optimised way. there is one universal format which works >>> on any >>> datetime format of any sql server. I forgeted that. can any one tell me >>> tht >>> so that it works on evry server. >>> >>> Kishor >> >> > Yes, SET LANGUAGE mean that you need to specify the month name in the correct language for the
alphabetic format. And the problem is indeed that the login for the connection can be different from the locale that the user is using (assuming the application generates the month name based on the Windows locale). -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "GregO" <grego@community.nospam> wrote in message news:O617v4ppFHA.1416@TK2MSFTNGP09.phx.gbl... > Hi Tibor > I had a thought about this last night. And I sure I'm on the right track here. > While I understand the issue you raise I'd just point out that if you were using the French > language you wouldn't enter the month in English you would do this > > SET LANGUAGE FRENCH > SELECT ISDATE('04 août 2005') > > SET LANGUAGE ENGLISH > SELECT ISDATE('04 May 2005') > > and both of these work. > The problem is that maybe the input would not be in the same language as the local settings and > that's when it would fail. > > kind regards > Greg O > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message > news:uhMSnSJpFHA.2504@tk2msftngp13.phx.gbl... >> Greg, >> >> I'm afraid that the alphabetic format is dependent on SET LANGUAGE (and a login's default >> language). >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "GregO" <grego@community.nospam> wrote in message news:%23H4Vt9IpFHA.1088@TK2MSFTNGP14.phx.gbl... >>> Hi, >>> As well as the ones David has saind I haven't had a problem with >>> DD MMM YYYY >>> >>> 04 May 2005 >>> >>> >>> -- >>> kind regards >>> Greg O >>> Need to document your databases. Use the firs and still the best AGS SQL >>> Scribe >>> http://www.ag-software.com >>> "kishor" <kis***@discussions.microsoft.com> wrote in message >>> news:CCBC9546-5EF7-458E-9528-475B757B4A0F@microsoft.com... >>>>I hav a very small application, which insert some date related data in sql >>>> server. >>>> >>>> But tht application while inserting date time values. on some server gives >>>> error. because datetime format of machine/server is different. I want to make >>>> my application compatible with any date time format on the server. >>>> >>>> This can be achieved by knowing format before executing the sql statement >>>> and aftr that statement is converted to tht format to avod error. but I think >>>> this is not a optimised way. there is one universal format which works on any >>>> datetime format of any sql server. I forgeted that. can any one tell me tht >>>> so that it works on evry server. >>>> >>>> Kishor >>> >>> >> > > > As well as the ones David has saind I haven't had a problem with Probably because you have only worked in US English systems.> DD MMM YYYY > > 04 May 2005 SET LANGUAGE FRENCH SELECT ISDATE('04 May 2005') SET LANGUAGE ENGLISH SELECT ISDATE('04 May 2005') Please read: http://www.aspfaq.com/2023 http://www.karaszi.com/SQLServer/info_datetime.asp thanks Aaron
Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:OjdKkrLpFHA.3380@TK2MSFTNGP12.phx.gbl... >> As well as the ones David has saind I haven't had a problem with >> DD MMM YYYY >> >> 04 May 2005 > > Probably because you have only worked in US English systems. > > SET LANGUAGE FRENCH > SELECT ISDATE('04 May 2005') > > SET LANGUAGE ENGLISH > SELECT ISDATE('04 May 2005') > > Please read: > http://www.aspfaq.com/2023 > http://www.karaszi.com/SQLServer/info_datetime.asp > Hi Aaron,
I had a thought about this last night. And I sure I'm on the right track here. While I understand the issue you raise I'd just point out that if you were using the French language you wouldn't enter the month in English you would do this SET LANGUAGE FRENCH SELECT ISDATE('04 août 2005') SET LANGUAGE ENGLISH SELECT ISDATE('04 May 2005') and both of these work. The problem is that maybe the input would not be in the same language as the local settings and that's when it would fail. kind regards Greg O Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:OjdKkrLpFHA.3380@TK2MSFTNGP12.phx.gbl... >> As well as the ones David has saind I haven't had a problem with >> DD MMM YYYY >> >> 04 May 2005 > > Probably because you have only worked in US English systems. > > SET LANGUAGE FRENCH > SELECT ISDATE('04 May 2005') > > SET LANGUAGE ENGLISH > SELECT ISDATE('04 May 2005') > > Please read: > http://www.aspfaq.com/2023 > http://www.karaszi.com/SQLServer/info_datetime.asp > In addition to the other posts, you might want to check out:
http://www.karaszi.com/SQLServer/info_datetime.asp -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "kishor" <kis***@discussions.microsoft.com> wrote in message news:CCBC9546-5EF7-458E-9528-475B757B4A0F@microsoft.com... >I hav a very small application, which insert some date related data in sql > server. > > But tht application while inserting date time values. on some server gives > error. because datetime format of machine/server is different. I want to make > my application compatible with any date time format on the server. > > This can be achieved by knowing format before executing the sql statement > and aftr that statement is converted to tht format to avod error. but I think > this is not a optimised way. there is one universal format which works on any > datetime format of any sql server. I forgeted that. can any one tell me tht > so that it works on evry server. > > Kishor  
Other interesting topics
|
|||||||||||||||||||||||
