Author |
Topic |
asm
Posting Yak Master
140 Posts |
Posted - 2005-11-13 : 23:51:51
|
HiHow can i set the date format to dmy for a entire databaseI am using VB6.my computer regional setting date is dd/mm/yyyythanksasm |
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-14 : 00:02:39
|
Hope this helps.. SET DATEFORMAT dmy???Complicated things can be done by simple thinking |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-14 : 00:03:04
|
Let it be as it is.Use Presentation layer to format the date to the format you wantMadhivananFailing to plan is Planning to fail |
|
|
asm
Posting Yak Master
140 Posts |
Posted - 2005-11-14 : 00:08:22
|
front end : date show as dmy but while give value and pass the sql statment then error message flash the server somthing like ' char conversation of date..."asm |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-14 : 00:19:22
|
well did u tried set dateformat..????Complicated things can be done by simple thinking |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-14 : 00:41:07
|
Let the Server format as it is.When you pass date from VB, use the format yyyymmddMadhivananFailing to plan is Planning to fail |
|
|
asm
Posting Yak Master
140 Posts |
Posted - 2005-11-14 : 07:03:40
|
But when i pass the date in VB: 20/10/20005 for generating a report sql statement flash error. Pls guide how can i convert user input date in dmy formar to sql server date yyyymmdd |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-11-14 : 07:18:06
|
sDate = Right("0000" & Year(sDate),4) & Right("00" & Month(sDate),2) & Right("00" & Day(sDate),2)Go with the flow & have fun! Else fight the flow |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-14 : 08:08:16
|
Format it to Format(datecol,"yyyymmdd")MadhivananFailing to plan is Planning to fail |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-11-14 : 08:42:33
|
Don't do any formatting of your dates -- pass in date values using date datatypes. Don't convert things to varchar so they look pretty. USE PARAMETERS. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-11-14 : 09:10:08
|
quote: Originally posted by madhivanan Format it to Format(datecol,"yyyymmdd")
that doesn't work always... it doesn't show correctly if you have english and american locales...at least in VBScript it doesn't work.Go with the flow & have fun! Else fight the flow |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-11-14 : 09:12:53
|
quote: Originally posted by jsmith8858 Don't do any formatting of your dates -- pass in date values using date datatypes. Don't convert things to varchar so they look pretty. USE PARAMETERS.
sometimes you have to parse them jeff...we had a lot of problems with that.our sql server has En-us language our users are from Slovenia, Germany, Spain, USA, Switzerland... all those dateformats get screwed up even if you put them in paramteres:1/4/2005 can be translated to april 1st or jan 4th...Go with the flow & have fun! Else fight the flow |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-14 : 09:13:41
|
>>at least in VBScript it doesn't work.Well. But In VB6 it works correctly MadhivananFailing to plan is Planning to fail |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-11-14 : 09:16:45
|
great for you dates shortened my nerves for an inch or two at least Go with the flow & have fun! Else fight the flow |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-14 : 09:16:51
|
quote: Originally posted by jsmith8858 Don't do any formatting of your dates -- pass in date values using date datatypes. Don't convert things to varchar so they look pretty. USE PARAMETERS.
In VB6, although you use Date Datatype it wont insert the date in correct format until you use any one of these formats yyyymmddyyyy/mm/ddyyyy-mm-ddMadhivananFailing to plan is Planning to fail |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-14 : 09:18:27
|
>>at least in VBScript it doesn't work.Yes because you dont have Format function there MadhivananFailing to plan is Planning to fail |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-11-14 : 09:19:57
|
there is FormatDateTime that does the same thing... only it doesn't really Go with the flow & have fun! Else fight the flow |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-11-14 : 11:24:30
|
quote: Originally posted by madhivanan
quote: Originally posted by jsmith8858 Don't do any formatting of your dates -- pass in date values using date datatypes. Don't convert things to varchar so they look pretty. USE PARAMETERS.
In VB6, although you use Date Datatype it wont insert the date in correct format until you use any one of these formats yyyymmddyyyy/mm/ddyyyy-mm-ddMadhivananFailing to plan is Planning to fail
I'm afraid I don't follow that ... what do you mean? Do you have an example? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-14 : 23:46:35
|
If the SQL Server date format is set to mdy and the regional setting of the client machine is set to the format d/m/yy then the dates sent from the Client without universal format will be treated differently by SQL Server so that it will assume 1st feb to 2nd Jan, 5th August to 8th may, etc. In VB6, if you assign the value to date variable as '05/08/2005' assuming that it is 5th August,2005 and if you dont format it using universal format then it will be stored in SQL Server as 8th May, 2005MadhivananFailing to plan is Planning to fail |
|
|
asm
Posting Yak Master
140 Posts |
Posted - 2005-11-15 : 00:46:25
|
Get the date in form as text and the same insert to table.I am not using format at the time of insert date data in the tableCan i use any format at the time of insert ?asm |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-15 : 01:05:24
|
>>Get the date in form as text and the same insert to table.What is the data type of your datecolumn?MadhivananFailing to plan is Planning to fail |
|
|
Previous Page&nsp;
Next Page
|