March 19, 2009 at 12:53 pm
My current format is 3/13/2009 12:00:00 AM
How do I select or change the actual format of the date to 3/13/2009.
Thanks
March 19, 2009 at 12:56 pm
you need to check BOL first. here is the answer with example.
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 does not affect the display of date values.
The setting of SET DATEFORMAT is set at execute or run time and not at parse time.
SET DATEFORMAT overrides the implicit date format setting of SET LANGUAGE.
Permissions
Requires membership in the public role.
Examples
The following example uses different date formats to handle date strings in different formats.
Copy Code
-- Set date format to month, day, year.
SET DATEFORMAT mdy;
GO
DECLARE @datevar DATETIME;
SET @datevar = '12/31/1998';
SELECT @datevar AS DateVar;
GO
-- Set date format to year, day, month.
SET DATEFORMAT ydm;
GO
DECLARE @datevar DATETIME;
SET @datevar = '1998/31/12';
SELECT @datevar AS DateVar;
GO
-- Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar DATETIME;
SET @datevar = '1998/12/31';
SELECT @datevar AS DateVar;
GO
:crazy: :alien:
Umar Iqbal
March 19, 2009 at 1:02 pm
Appreciate your reply.
Can we change the datetime setting for a particular table or database without effecting other data (table if we are setting for only 1 table or databases if we are setting for only 1 database) and ofcourse only if this option is available where we can set for only certain objects (tables ) or some databases on a server .
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply