July 17, 2006 at 12:38 pm
Just a few days ago I was using the convert command without issue and changing the date format using the three digit 'style' at the end of the convert statement. However today it seems that it is not working correctly. For example,
If I use this command I get:
SELECT CONVERT(CHAR(10), GETDATE(), 101)
->07/17/2006
When I try to insert into a table (tablename) I get:
Insert tablename(date)
Values ( convert (datetime, getdate(), 101))
select * from tablename
->2006-07-17 14:26:43.807
Shouldn't the 101 at the end of the statement change the format of the date to 07/17/2006? Like I said this was working on Friday and now it is not. Anyone have any ideas or am I just crazy?
July 17, 2006 at 12:45 pm
Your first parameter, (* CHAR(10) *), in ur first SQL truncates the time portion where it does't in ur 2nd SQL.
July 17, 2006 at 12:54 pm
That can't be right because even if I use:
SELECT CONVERT(CHAR(50), GETDATE(), 101)
->07/17/2006
July 17, 2006 at 1:03 pm
I figured it out. My previous table had the date field set as varchar(255) and my current date field is set to datetime, changed it to varchar(255) and the dates import correctly.
July 18, 2006 at 6:39 am
Actually, you've got it backwards. You should use the datetime data type in your table and convert for display purposes only.
GO
CREATE TABLE #temp (id int, dt datetime)
GO
INSERT #temp(id, dt) VALUES (1, GetDate())
SELECT dt
, CONVERT(varchar(10), dt, 101) AS FormattedDate
FROM #temp
DROP TABLE #temp
July 18, 2006 at 6:50 am
How can I import the date into the table and store it in a different format if I use datetime as the datatype?
July 18, 2006 at 7:10 am
The datetime data type is stored in only one format. You can use CONVERT of CAST to display it is various formats using the SELECT statement.
To insert dates, you have a number of formats available, which vary slightly depending on the servers regional setting (for example USA - m/d/y, Europe - d/m/y).
Referring to the previous example, in the USA I can use these formats:
INSERT #temp(id, dt) VALUES (1, '7/17/2006')
INSERT #temp(id, dt) VALUES (1, '20060717') -- this works all the time, and is the best format in my opinion
I suggest you read about the datetime data type in books online.
Also, read about the SET DATEFORMAT statement.
-- New example
GO
CREATE TABLE #temp (id int, dt datetime)
GO
SET NOCOUNT ON
INSERT #temp(id, dt) VALUES (1, GetDate())
INSERT #temp(id, dt) VALUES (2, '7/17/2006')
INSERT #temp(id, dt) VALUES (3, '7/17/2006 5:10:24')
INSERT #temp(id, dt) VALUES (4, '2006-07-17')
INSERT #temp(id, dt) VALUES (5, '2006-07-17 6:15:30')
SET NOCOUNT OFF
SELECT dt
, CONVERT(varchar(10), dt, 101) AS FormattedDate
FROM #temp
DROP TABLE #temp
July 21, 2006 at 8:12 am
mmmm.. the date formating issue again. i once had many nightmares sometime back about this formating issue.
i ended up using the format suggested by mkeast.
INSERT #temp(id, dt) VALUES (1, '20060717') -- this works all the time, and is the best format in my opinion
and it did work well after that
July 24, 2006 at 2:24 pm
The problem I had was that I am importing from an external source using flat files. Our external source stores entries with no date as 00-XXX-00. The 00-XXX-00 values would not import into a datetime datatype without throwing an error so what I did was import the entrie flat file into a temp table which uses varchar(255) for all of the data types. I then import again into another temp talbe which uses varchar(255) for all of the data types except I use the following (thank you to member Phil Parkin for this) to insert NULLs when the 00-XXX-00 is encountered:
CONVERT(VARCHAR(255), CAST(nullif([Order date],'00-XXX-00')AS DATETIME), 111)[Order date]
I then import that table into the production table with all the appropriate data types including datetime and have a working table with correct values.
I couldn't figure out how to add the CONVERT... statement into my dtsx package when I'm importing from a flat file into a table so that's why I have two temp talbes. My dtsx package contains a dataflow which imports the flat file into temp_table1 and then has two Execute SQL Tasks in the control flow, one which imports temp_table1 into temp_table2 converting the dates and adding NULLs and then one that imports temp_table2 into my production table. I get datetime inserted correctly and NULLs where needed. It's kinda ugly but it works.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply