Date column Auto Convert on INSERT
-
Hi,
I have a date columns in my tables and I wish to maintain this format MM/DD/YYYY 00:00:0000.
I want to place a an auto conversion on the column so that when any one INSERTS a date like this
05/15/2005 04:90:0012 ( ie MM/DD/YYYY HH:SS:MMSS)
Or 15/5/2005 (ie DD/MM/YYYY)
or 2005/15/5 (ie YYYY/DD/MM)
it gets automatically converted to (MM/DD/YYYY)
05/15/2005 00:00:0000
Like SELECT CAST(CONVERT(char(10), CURRENT_TIMESTAMP, 101) AS datetime)
Any ideas
Thanks
-
The filtering and validation should be done on the client side - can you not do this ? Splly. the dd & mm..
**ASCII stupid question, get a stupid ANSI !!!** -
How are rows inserted into that table? If this happens via a stored procedure, I would set the time there to midnight. Another option would be a trigger that runs over the inserted rows.
The display format is completely different from the underlying internal format and depends on your computer settings. You can't store in a specific format.
See if this helps:
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.sql-server-performance.com/fk_datetime.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
My Language Setting on SQL server is English
Frank, So what you are saying is this, please correct me if I am wrong
Currently, I have set up my date fields as RunDate with they data type SMALLDATETIME
So when ever I INSERT Data to this field from a source I make sure that date is in format "YYYYMMDD" from the sending source.
Are there any Database options settings that I must change to make sure this happens.
Question:What would the check constraint look like to maintain this format ?
Question:Why did this happen in my TEST database ? Row 1 is date format as I require '19561030', but the date format in the table is '1956-10-30 00:00:00.000', should it not be '19561030 00:00:00.000' ?
Ref:http://support.microsoft.com/default.aspx?scid=kb;en-us;173907
CREATE TABLE X(D DATETIME)
INSERT INTO X VALUES ('19561030')
INSERT INTO X VALUES ('561030')
INSERT INTO X VALUES ('10/30/1956')
INSERT INTO X VALUES ('10/30/56')
INSERT INTO X VALUES ('30 OCT 1956')
INSERT INTO X VALUES ('30 OCT 56')
INSERT INTO X VALUES ('OCT 30 1956')
INSERT INTO X VALUES ('OCT 30, 1956')
INSERT INTO X VALUES ('OCT 30, 56')
INSERT INTO X VALUES ('OCTOBER 10, 1956')
SELECT * FROM X
Returns...
1956-10-30 00:00:00.000
1956-10-30 00:00:00.000
1956-10-30 00:00:00.000
1956-10-30 00:00:00.000
1956-10-30 00:00:00.000
1956-10-30 00:00:00.000
1956-10-30 00:00:00.000
1956-10-30 00:00:00.000
1956-10-30 00:00:00.000
1956-10-10 00:00:00.000
-
The format "YYYYMMDD" is considered a safe date format, that will work virtually everywhere no matter what the specific regional settings of the machine are. This is more important when you INSERT the data or do some calculcation. It si of less importance when you present the data. If you have influence on how the source data comes in, I would opt for this format. However, if you don't have any influence on the source data, but this data complies with the english date format, that should also work. Now, once you've inserted the data into a DATETIME column (or SMALLDATETIME, for that matter), the data looks something like this
DECLARE @dt DATETIME
SET @dt='1956-10-30 00:00:00.000'
SELECT CAST(@dt AS BINARY(8))
------------------
0x0000511400000000
(1 row(s) affected)
There is no need for a CHECK constraint to maintain this format. It will transformed into a readable format when queried. AFAIK, there is no user control over this behaviour, no setting or whatsoever.
As for your second question:
SELECT
CONVERT(DATETIME,D,112)
, CONVERT(CHAR(8),D,112)
, ISDATE(D)
FROM
X
might make things more clear. All your different data can be transformed into a valid DATETIME. Now, try to INSERT these rows:
INSERT INTO X VALUES ('30.10.1956')
INSERT INTO X VALUES ('30.10.56')
It should fail.
However, there are countries like mine where this is a valid format. And when I change your statement to
SET DATEFORMAT dmy
SET NOCOUNT ON
CREATE TABLE X(D DATETIME)
INSERT INTO X VALUES ('30.10.1956')
SELECT
CONVERT(DATETIME,D,112)
, CONVERT(CHAR(8),D,112)
, ISDATE(D)
FROM
X
DROP TABLE X
SET NOCOUNT OFF
I can run the batch without problems.
If you have some time, read the articles I've mentioned above. I would first read Tibor's and then mine. Tibor focusses more on this dateformat thing, while I focus more on tips and tricks.
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If it truly is a "DateTime" column, it just doesn't matter what format you store it in so long as it is one recognized by SQL. It will have the date and, in this case, a time of 00:00:00.000. Dates and times are NOT stored in any particular format. Only when they are retrieved can you format them. And, it would be a huge mistake in judgement to store dates in a character based field.
If you have a GUI that's has a field for entering these dates, the format should be checked there and not in SQL because SQL really doesn't care so long as it's a date that can be recognised. Again, if it's a datetime field, you will have to reformat any time you return values from the field.
--Jeff Moden
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply