June 21, 2011 at 9:25 pm
This question has been asked probably 1000 times already but nowhere have I seen a clear answer (assuming there is one).
As you know,SQL server puts the date into this format :
YYYY:MM:DD:SS (or something similar...).
Since I do not live on the moon,I dont fancy reading the date backwards
with useless seconds attached.Nor do I fancy doing conversions anywhere.
Problem gets annoying when doing an INSERT query.
Since the guys at Microsoft find it hard to make SQL Server use regional settings, what is there to do to insert the date format in DD:MM:YYYY ?
This (!) is REALLY stupid ... Nearly all banana databases use DD:MM:YYYY ... Microsoft,how about giving a user a choice ?
June 21, 2011 at 10:41 pm
Will this make you happy?
SET DATEFORMAT ymd;
GO
DECLARE @datevar DATETIME;
SET @datevar = '1998/12/31';
SELECT @datevar AS DateVar;
GO
-- Result: 1998-12-31 00:00:00.000
--Will this make you happy?
SET DATEFORMAT dmy;
DECLARE @datevar DATETIME;
SET @datevar = '31/12/1998';
SELECT @datevar AS DateVar;
--Result: 1998-12-31 00:00:00.000
or how about this:
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
--Result: 22/06/2011
June 21, 2011 at 11:34 pm
the below link shows thes different styles to which you can convert a date time to
http://msdn.microsoft.com/en-us/library/ms187928.aspx
The regional setting are provided but in this case it means
1/ 12 / 2011 can be UK 1st of Dec 2011 or Jan 12th 2011 in US , the international ISo standard is what is used by SQL in which the year come first followed by the month and then day, acutal punctuations are - or / and : for time
June 22, 2011 at 5:05 am
So how do I use it in this query ?
USE MYDATABASE
INSERT INTO MY_TABLE (TYPE,START_DATE,END_DATE,RATE)
VALUES
('DBLMS','2011.03.01','2011.04.16','104');
????
June 22, 2011 at 6:17 am
skynet_si (6/22/2011)
So how do I use it in this query ?USE MYDATABASE
INSERT INTO MY_TABLE (TYPE,START_DATE,END_DATE,RATE)
VALUES
('DBLMS','2011.03.01','2011.04.16','104');
????
What do you want to do in the query?? do you want to insert values into the table?? or u want to get the value in some specific format after u insert the value...
if u want to insert the value ur query works just fine.. in case u need more clarifications, provide DDL statement for your table(s) along with DML statements for sample data
June 22, 2011 at 6:35 am
Convert thing just converts data view - it leaves field intact.
Converts data (date type) to string.
This is done just to view data.
However,the data (datetime) itself unless in the crazy format YYYY:MM:DD
can not be entered. Or am I missing something ...
I want to INSERT data of course (I imagine INSERT serves for the purpose...)
This will NOT work :
USE MYDATABASE
INSERT INTO MY_TABLE (TYPE,START_DATE,END_DATE,RATE)
VALUES
('DBLMS','01.03.2011','16.04.2011','104');
START_DATE,END_DATE are datetime fields,of course...
So very simple question is this :
How do I insert my version of date (DD:MM:YYYY) in a funny database
that accepts only YYYY:MM:DD ???
June 22, 2011 at 7:10 am
This will NOT work :
USE MYDATABASE
INSERT INTO MY_TABLE (TYPE,START_DATE,END_DATE,RATE)
VALUES
('DBLMS','01.03.2011','16.04.2011','104');
Default date format for conversion is dependant language setting for the login.
e.g.
British English accepts DD.MM.YYYY and YYYY.DD.MM
English accepts MM.DD.YYYY and YYYY.MM.DD
Far away is close at hand in the images of elsewhere.
Anon.
June 22, 2011 at 7:14 am
Does this help:
CREATE TABLE #MY_TABLE (TYPE VARCHAR(5),START_DATE DATETIME,END_DATE DATETIME,RATE VARCHAR(4))
SET DATEFORMAT DMY --Note this setting
INSERT INTO #MY_TABLE (TYPE,START_DATE,END_DATE,RATE)
VALUES('DBLMS','01.03.2011','16.04.2011','104');
SELECT TYPE, CONVERT(VARCHAR(10), START_DATE, 103) AS [DD/MM/YYYY]
FROM #MY_TABLE
Result:
TYPEDD/MM/YYYY
DBLMS01/03/2011
Remember that:
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.
Source for the above quotation:
June 22, 2011 at 7:22 am
June 22, 2011 at 10:35 am
so basically I need this :
SET DATEFORMAT DMY
before every insert queery.
I dont really care how the server stores my data as long as I enter it
correctly....
I tried and it works ...
Thank god !
June 22, 2011 at 10:48 am
skynet_si (6/22/2011)
so basically I need this :SET DATEFORMAT DMY
before every insert queery.
I dont really care how the server stores my data as long as I enter it
correctly....
I tried and it works ...
Thank god !
Also keep in mind:
This setting affects the interpretation of character strings as they are converted to date values for storage in the database. It does not affect the display of date data type values that are stored in the database or the storage format.
and:
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.
June 23, 2011 at 12:52 am
Thank you all ...
But ...since everything seems to be going well, I have obviously overlooked something.....
😛
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply