I have a Column called BirthDate that displays birthdays in this format 11/01/2015 and I need the Birthdate column to be updated to Nov 01 2015 12:00:00AM
Dates have no display format. Strings have.
What you are asking MUST be done in the presentation layer, I.E. your application
Thats incorrect This SELECT DATENAME(MONTH, GETDATE())
+ RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) gives me
March 16,2017 I am looking for
Mar 16,2017 format Anything can be done in SQL
Just because you can, doesn't mean you should. You are asking to UPDATE the value to a different format, so I infer that you are storing the date as a string, which is a terribad idea.
Dates must be stored in date/datetime/datetime2 columns, not in character-based columns.
Even if you are storing your dates in a date column and all you want is SELECT the data back using a specific format, don't do that on the database server side: let the application format the date using the client computer's locale.
This is not an application forum if you dont know the answer better to not post. This is for an SSIS package this ETL is gathering bunch of dirty data doing a conversion and then normalizing it accordingly. Now again I just need answer to the initial question.
SELECT CONVERT(varchar, CONVERT(datetime, Birthdate), 100)
100 format does the trick
Got it myself
Thanks
You had your answer, but didn't like it, which is really unfortunate. Your aggressive attitude won't help you get more answers and your are missing the opportunity to learn something from the answers you already had.
Forums are for others to help in the technology that the user is using. I could have used numerous scripting languages to achieve the desired results. However this was strictly for SQL for a reason. NO APPLICATION is tied to this piece of work. I appreciate the recommendation have a great day!
SSIS is an application. You could have set the format in your package.
However, this is not the main concern: where is that package writing to? A file? A database?
If it's a database and you need a particular date format, that can only mean that you are storing the date as its human readable representation, which is not a good idea. You may want to read my article from 2012 on this topic: http://www.sqlservercentral.com/articles/T-SQL/88152/
If the target is a file, you should use an ISO format (such as ISO8601) rather than a nonstandard format.
So, going back to your initial question, maybe CONVERT with 100 style can do the trick right now, but it's probably contributing to a bad design. I have no desire to have the last word on this, I'm genuinely trying to help you, even if you don't seem to like it.
Also, remember that forums are not just for the OP, but also for other readers that may run into the same situation and seek for an answer, so pointing out the fallacies of dealing with date formats is not just in your best interest, but also in everybody's best interest.
How did your 1st post help? Please answer that? Especially when their is no application that this is tied to. These are staging tables that being cleaned for a temporary solution. This is not a long term solution. It was a simple question that was needing a direct answer. Yes these forums help everyone. In this day in age users like to see quick viable solutions not what you gave.
Sean, you can either blame me for whatever you find appropriate or learn
something, the choice is yours.
How am I blaming you? I asked you question that you cant answer? Its all good. We can agree to disagree.
Presumably Birthdate is String and if so there are all types of grief associated with assuming that it will convert to DATETIME (presumably it doesn't actually have a TIME component? so as a side issue probably better to convert it to DATE).
Unless the string date is in UNambiguous format (with for DATETIME only includes YYYYMMDD or "YYYYMMDD hh:mm:ss" if there is a time element, and the ISO format YYYY-MM-DDTHH:MM:SS, and for DATE datatpye also YYYY-MM-DD) SQL will parse the string according to a variety of ambiguous date format rules which take into account things like the Language of the currently connected user - so it is prone to change and then given unexpected results.
In SQL you can force date format parsing using SET DATEFORM mdy or whatever, although I have no idea how to do that in SSIS.
IMHO better not to make any assumptions about date parsing and explicitly parse it yourself (unless SSIS has some UNambiguous date parsing formats that you can explicitly utilise).
For the rest, as @spaghettidba said, once it is in a DATE / DATETIME datatype object (Column / @Variable / whatever) then best to keep it in that format until it gets to the presentation layer. IME not doing that leads to grief ... sooner or later ..