Hi,
I have a varchar column with mixed date formats, some are dd-mm-yyyy and others are mmm dd yyyy hh:mmAP, these latter having been generated automatically by SQL when I changed the column datatype from datetime to varchar.
I would like to run an update script to pattern match the latter and change to the former.
I've been trying something along the lines of
update dwsubmit set authoreddate = (select day(authoreddate))+'-'+(select month(authoreddate))+'-'+(select year(authoreddate)) where id = 841
to try and get the format conversion correct, but this is obviously not correct. I have tried a few combinations, but have had no joy. Any ideas?
Thanks, Matt.
5 2386
I understand ur pain man....but u r doin it wrongly. Try this:
update dwsubmit
set authoreddate = (select datepart(authoreddate,dd))+'-'+(select datepart(authoreddate,mm))+'-'+(select datepart(authoreddate,yy))
where id = 841
op it works for u....av fun!
sorry i mixed it up
update dwsubmit
set authoreddate = (select datepart(dd,authoreddate))+'-'+(select datepart(mm,authoreddate))+'-'+(select datepart(yy,authoreddate))
where id = 841
it should be ds way....sorry about dat...
Thanks for that - I tried something similar, but SQL is actually adding the values up to come up with 2022 (04 + 12 + 2006). I have no idea what it is doing with the '-' characters. I then tried casting each part to char, but it ignores that too!
i op u tried d 2nd version not d first....ol d same
try ds....
update dwsubmit
set authoreddate = select datepart(dd,authoreddate) + '-' + select datepart(mm,authoreddate) + '-' + select datepart(yy,authoreddate)
where id = 841
Using what you gave me, I got to the following:
update dwsubmit
set authoreddate = cast((select datepart(dd,authoreddate)) as char(2))+'-'+cast((select datepart(mm,authoreddate)) as char(2))+'-'+cast((select datepart(yy,authoreddate)) as char(4))
where id = 841
Which gives me what I want unless there is only a single digit for the month or day, in which case I get something like 4 -12-2006 which should actually be 04-12-2006.
I'm sure the convert function is a better way of doing this, but I can't figure it out!
Thanks,
Matt.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: max |
last post by:
I am trying to access a foxpro database, using odbc all is well except
that dates prior to 1970 are not returned as valid usable data, so
would like to use ADO. When I select data containing...
|
by: ree32 |
last post by:
I am having problems with Date Strings when using VS.net method of
converting an XML document to a dataset.
I need the the date nodes to be of date type. As I am loading the
Dataset to a...
|
by: Scott Kinney |
last post by:
I have a database that I imported from a csv file (I know, all the stories
start out this way...)
The dates were given as text strings of the form 'yyyymmdd', and were
imported as text.
Not all...
|
by: Dana Shields |
last post by:
I am attempting to upsize from access to SQL Server. I'm trying to convert
my queries to SQL Server views; however, I'm having a lot of difficulty with
the syntax differences.
For instance, a...
|
by: zshenk |
last post by:
I need help converting dates. Currently, I have several tables and
databases that use the format 1/20/2007. I have a table right now
that uses the 20070120 format, and I was hoping that someone...
| |
by: py_genetic |
last post by:
Hello,
I'm importing large text files of data using csv. I would like to add
some more auto sensing abilities. I'm considing sampling the data
file and doing some fuzzy logic scoring on the...
|
by: HONOREDANCESTOR |
last post by:
I've been converting a dotnet dll to a com object so that it can be
called from vb6. If I want to pass a structure to a routine in the
com object, like this:
Call MyRoutine(byref MyStruct as...
|
by: Jef Driesen |
last post by:
How can I convert a date string to a number (e.g. a time_t value or a tm
struct)? I know about the strptime function, but then I have to know the
format string. And that is a problem.
I'm trying...
|
by: cloh |
last post by:
I have a form in Access that generates an Excel Workbook with multiple worksheets. Each worksheet is unique to a particular location. The top row of each sheet shows the dates, the left-most column...
|
by: Keith Hughitt |
last post by:
Hi,
I am having a little trouble figuring out how to convert a python
datetime to UTC. I have a UTC date (e.g. 2008-07-11 00:00:00). I would
like to create a UTC date so that when I send it to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |