Date convert
-
I have two date fields showing up as 'March 13, 2007 00:00:00', they are varchar (imported from a downloaded CSV file)
I can convert it into datetime by using the 'convert' statement, but am unable to update to insert that converted format
Here's my statement
SELECT [Email], [OptInDate], [OptedOut], [OptInDetails], [EmailType], [AOLSegment], [Address],
[City], [DBSource], convert(datetime, datein) as datein, [FirstName], [Flr22_Segment], [Industry], [LastName],
[Last_Clicked_Date], convert(datetime,Last_Open_Date) as Last_Open_Date,[Last_Sent_Date] , [LeadType], [Send_Hour_Reply],
[State], [ZIP] into masternew
FROM [SilverPop].[dbo].[NEWMaster]
-
Did you read Jeff's comment to your previous topic?
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=389085
_____________
Code for TallyGenerator -
Yes, I read the posting and was able to get the results I needed in the select,
but I can't actually modify the column during export using that select statement (syntax error converting datetime from character string) or use it in an update statement.... (same error)
How can I actually modify the existing data in the table?
I didn't see the answer to that in my prior post.
-
Which table?
What's the datatype for date column in that table?
Are you doing import (as you said in first post) or export (as you said in the next one)?
_____________
Code for TallyGenerator -
As stated in my previous post below... I need to export the data from SQL Server so I can import it into a 3rd party software....
I was trying to insert into a newtable from the source table using the convert, but it erred out.
The only fields I'm still having an issue with are varchar...
But don't worry about it... Sounds like your a little miffed at this point.
I'll find my answers elsewhere!!
I currently have a date column showing up as 'Mar 20 2007 3:57pm' and need it to be
'2007/03/20' so I can import it into a third party application.
Can I make that modification directly to the column or do I need to do a convert while exporting the data?
If I can do it at the column level, would it be done via an update statement? or alter on the table?
Thanks in advance
Susan
-
Is the column in the table of datetime datatype??
If so, why can't you simply open a connection on the remote APP directly to the server, or even a linked server between the servers and do a direct import?
-
1st you need to convert string 'Mar 20 2007 3:57pm' to datetime.
You cannot do it in update because your column is "varchar", not "datetime" as it should be.
To convert it properly you need to use style for CONVERT function. See topic "CAST and CONVERT" in BOL.
Then you need to convert received datetime value into string using another style.
You may find the style you need in the same topic.
If your converts will still fail you need to check your data and take care about the rows where string does not represent date in proper format.
_____________
Code for TallyGenerator -
No, no... he's not miffed... it's a bit of a language barrier thing and, because of that, he comes off as being short... He's really trying to help you think outside the box...
If you need to export and the date is in the CHAR based format that you say it is, then do this as part of the export code (from previous post)...
SELECT CONVERT(CHAR(10),CAST(yourdatestringcolumn AS DATETIME),111)
FROM yourtable
...you can export the "bad" dates as nulls if you wish...
SELECT CASE
WHEN ISDATE(yourdatestringcolumn) = 1
THEN CONVERT(CHAR(10),CAST(yourdatestringcolumn AS DATETIME),111)
ELSE NULL
END AS TheDate
FROM yourtable
Of course, it would be beneficial to fix all of the bad dates and you can find them all like this...
SELECT
FROM yourtable
WHERE ISDATE(yourdatestringcolumn) = 0 --Has a bad date
--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)
Intro to Tally Tables and Functions -
I have been using this site for many years and do appreciate all the help I get. Thanks for your comments and help.
Susan
-
Jeff, that would be
... ISDATE(...) = 0
right?
N 56°04'39.16"
E 12°55'05.25" -
You can tell him up front... he's not gonna mind.
JEFF YOU SCREWED UP!
. -
LMAO!!! Thanks guys... you were right and I corrected the entry.
--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)
Intro to Tally Tables and Functions -
Just to spoil everybody's celebration.
ISDATE sometimes is not good enough.
It's good if you locked inside of the world of American standards.
But imagine situation when you need to import files generated by American, German, Australian and all other sorts of origin software.
All at the same day, all into the same database.
For some of them string '13/08/2007' is absolutely legitimate date, for others - not quite.
Because of that ISDATE will return false results for some of files.
And you need to distinguish when it's wrong format causing misinterpretation in some rows of the file (in this case all other rows must be interpreted differently as well), and when it's just wrong data in the string (to be ignored according to the business rules).
Hello, globalism!
_____________
Code for TallyGenerator -
Yep... gotta agree... That's pretty much the reason for NOT storing dates as VARCHAR...
--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)
Intro to Tally Tables and Functions
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply