Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 setting the date

Author  Topic 

asm
Posting Yak Master

140 Posts
Posted - 2005-11-13 : 23:51:51
Hi

How can i set the date format to dmy for a entire database

I am using VB6.

my computer regional setting date is dd/mm/yyyy


thanks

asm

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
Go to Top of Page

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 want

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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 yyyymmdd

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-11-14 : 08:08:16
Format it to Format(datecol,"yyyymmdd")

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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
Go to Top of Page

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

yyyymmdd
yyyy/mm/dd
yyyy-mm-dd

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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
Go to Top of Page

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

yyyymmdd
yyyy/mm/dd
yyyy-mm-dd

Madhivanan

Failing to plan is Planning to fail


I'm afraid I don't follow that ... what do you mean? Do you have an example?
Go to Top of Page

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, 2005

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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 table
Can i use any format at the time of insert ?

asm
Go to Top of Page

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?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Page&nsp;  Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site