More junk with dates
-
I have a bunch of large files to import into SQL2005. I want to use SSIS to try to make this painless. The files I am importing have a date format like this;
"09/23/05 12:00 AM"
I need to get the four year century which the DBdatetime will give me when the import happens. I also want to drop the time so all I want is "09/23/2005". I have a huge amount of date fields in this file so the least painless is what I am looking for.
Thanks,
Dave
-
use the below command to leave the time
convert(varchar(25),columnname,101)
Dont forget to change the columnname to your column name in the table
Regards..Vidhya Sagar
SQL-Articles -
David,
"Dropping the time" should be done on the display side of things... not on the import side of things. Reason being is that the only way you can truly "drop the time" is to convert the dates to CHAR or VARCHAR and for more reasons than you can shake a stick at, that's the wrong thing to do.
With that in mind, I strongly recommend that you import the date fields into DATETIME columns... you and your server will love the idea later on when the boss says "I need a report by the following months and years" later on...
Also, if you post or attach a file with the first ten rows from one of the larger files of data you're trying to import, perhaps we can show you a very high speed method of importing the data.
Of course, if this is an ad-hoc one-off import, no need to go for performance but you should still import the date fields into DateTime columns.
--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) -
Jeff Moden (10/27/2007)
David,"Dropping the time" should be done on the display side of things... not on the import side of things. Reason being is that the only way you can truly "drop the time" is to convert the dates to CHAR or VARCHAR and for more reasons than you can shake a stick at, that's the wrong thing to do.
This comment makes a lot of assumptions about the requirements - how do you know OP doesn't need the data in VARCHAR format? How do you know the boss didn't already say "whatever you do, don't include the time components of the dates"?
If the data is in fixed-width files, the absolute easiest way to exclude the time components is to not import it at all when setting up your column definitions. However, if the data is delimited then the simplest way is probably a convert or cast function call placed in the SQL statement of the data source.
One thing to be careful about - when importing dates from text files, if you are converting to a date format then you run the risk of invalid date data screwing up your successful package execution - keep that in mind when designing your packages. for example, what if your data contains one row with the data "09/31/2007" in the text file? if you're doing a one-off then this probably doesn't matter.
-
It's never good to store a date as a VARCHAR... if you want VARCHAR, you would do that in a SELECT... not as a matter of storage.
--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) -
Yes, Jeff does make assumptions, but they are the correct assumptions to make.
Blindly following a statement like "whatever you do, don't include the time components of the dates" doesn't make sense. How much advantage would there be in storing the date of 10/27/2007 in say a varchar(10) field as opposed to a datetime field? How many disadvantages are there??
Assume away Jeff ...
--------------------
Colt 45 - the original point and click interface
-
I would import the information as a datetime. This will give you the 4 digit year during import. You can also do a lot of date/time manipulation with dates and almost none with strings.
If you have a date/time field be ready to use SQL functions such as datepart, datediff, dateadd, (datevariable +1) to add a day, and time stripping such as
DATEADD(d, DATEDIFF(d,0,dbo.tblCDR.LocalStartTime), 0) as DayOfCall
If you have dates stored in string type fields be prepared to create your own SQL functions like CONVERT(DATETIME, YourStrDate) which you will have to use every time you touch this variable. This will give you a date in SQL like 2007-10-28 00:00:00.000 AM which is what you took great pains to remove. At this point you still have to deal with a time stamp, but its just a default time instead of your original time stamp.
There is nothing better than original data. You can always destroy it or gum it up later for some reporting request, but at least you will have that as an option.
-
in ssis, use expression and write a function to segregate the datetime into the required format what you need.. Please check for MSDN for samples/solutions.....
-
Jeff Moden (10/29/2007)
It's never good to store a date as a VARCHAR.Never? Absolutely never?
I think I'd say almost never.
For example, maybe you're loading a warehouse staging table from a source system that stores dates as ISO-formatted strings (YYYYMMDD). In this case I might keep the date as a char or varchar in my staging table. One of the goals of a stage load should be to always work, even if there is bad data. If you try to perform the conversion when importing to the stage table, you may get errors, which could require you to re-run part of your staging load. This could be bad, e.g. if your load process puts a lot of strain on the source system. In this case I'd probably keep a varchar date column in my staging table and convert it during the next loading step, where any errors would be handled. Obviously this isn't the only way to accomplish this, but it represents a scenario where it might make sense to store (at least temporarily) a date in a character format.
what if your task is to import a large volume of data from a flat file, run some SQL against it (not involving the dates), and then export it to another flat file? I have this exact scenario at a client site now. Since I'm only temporarily storing the data, I'm not going to mess with trying to convert the 10+ date fields into datetime datatypes, just to have to re-convert it on the way out again. The source of the data performs checks on the validity of the date data during the export. The purpose of this task is to read in an interface file, make some changes to it, and re-export it to the identical format. why would I want to mess with the dates at all in this case? storage space? that isn't really significant to me compared to the processing overhead of two unnecessary conversions x 10+ date fields.
Another reason to store a date as a varchar - date-time values that exceed the accuracy or range limits of SQL Server. It looks like 2008 will fix many of these issues, but if you need date data more accurate than SQL Server permits, then datetime just doesn't work.
Of course, you could argue that a numeric format is better than varchar for some of these purposes, and I wouldn't necessarily disagree with that. I also recognize that there are many benefits to storing dates as datetimes - I'm not at all against using datetime datatypes.
My point was that sometimes (perhaps even rarely in this case) requirements should make you think about alternative solutions. Way too many database professionals today suffer from thinking in terms of absolutes. I've tried to remove the words "always" and "never" from my vocabulary in this context. Let's try to build solutions that best solve the problem, instead of constraining ourselves with rigid rules that aren't 100% universal.
I don't know if the OP's problem is best solved with a varchar (probably not). Sorry if I'm dragging the thread off-topic, my point was really more about the requirements assumptions.
-
And, you've validated the dates how?
I normally don't say "never" to anything... but this would be one of them.
--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) -
you're validating data that's already been validated? why?
-
BaldNomad (10/29/2007)
you're validating data that's already been validated? why?According to the OP, the data is in a file... much like storing dates as datetimes, I always valid even supposedly validated files... I just don't trust 3rd party data even when the 3rd party is internal. Has paid off many times.
--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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply