Set Date Format - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Set Date Format

Author

Topic

dodi
Starting Member

9 Posts

Posted - 2003-12-30 : 09:01:28

Hi All

In Oracle i set the date format like this
Alter session set NLS_DATE_FORMAT='MM/DD/yy';
How do i do it in SQL

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-30 : 09:12:31

close enough:

SET DATEFORMAT mdy

valid options include dmy, mdy, ymd, etc. I don't really know what that command does in Oracle, but note that in SQL Server, this does not affect the display of dates or the formatting of dates. It only affects the order in which character strings are parsed when converting to datetime.

OS

dodi
Starting Member

9 Posts

Posted - 2003-12-30 : 09:34:14

1.Is this function sets the Sql to work with mdy in insert and select
2.do i need to return to default after i finish or it's only for my connection

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-30 : 09:59:44

Why do you want to do this?


USE Northwind
GO

CREATE TABLE myTable99(Col1 datetime)
GO

DECLARE @x varchar(25)

SET @x = '12/31/03'

INSERT INTO myTable99(Col1) SELECT @x UNION ALL SELECT GetDate()

SELECT CONVERT(varchar(10),Col1,1), Col1 FROM myTable99

DROP TABLE myTable99
GO

Brett

8-)

dodi
Starting Member

9 Posts

Posted - 2003-12-30 : 10:03:53

SO i will explain my problem
In my firm we wrote a 4 projects for SQL in usa date format
i can not fix now the program to work with different format it's to much code to fix.
so in oracle i can solve the problem with :
Alter session set NLS_DATE_FORMAT='MM/DD/yy';
and now the 4 progrmas work good

What can i do in Sql with out changing the Code

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-30 : 11:00:18

Do you have sql server client tools installed?

Look it up in Books Online...

quote:

SET DATEFORMAT
Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.

Syntax
SET DATEFORMAT { format | @format_var }

Arguments
format | @format_var

Is the order of the dateparts. Can be either Unicode or DBCS converted to Unicode. Valid parameters include mdy, dmy, ymd, ydm, myd, and dym. The U.S. English default is mdy.

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.

That makes mention for entry of values...not getting them out...

In any event you don't have to do anything to get the data in, in the format you showed us (look at my sample code...did you execute it?)

And how is it too much code to change? Did you write it all and never test?

Do you use Query Analyzer?

You can even use UPDATE with mm/dd/yy...but to SELECT and DELETE, you either need to use CONVERT or do ranges...

Anyone else have a fact/opinion on this?


USE Northwind
GO

CREATE TABLE myTable99(Col1 datetime)
GO

DECLARE @x varchar(25)

SET @x = '12/31/03'

INSERT INTO myTable99(Col1) SELECT @x UNION ALL SELECT GetDate()

SELECT Col1 FROM myTable99

SELECT CONVERT(varchar(10),Col1,1), Col1 FROM myTable99

UPDATE myTable99 SET Col1 = '12/31/01'

SELECT CONVERT(varchar(10),Col1,1), Col1 FROM myTable99

DROP TABLE myTable99
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources