I have some columns of data in SQL server that are of NVARCHAR(420)
format but they are dates. The dates are in DD/MM/YY format. I want to
be able to convert them to our accounting system format which is
YYYYMMDD. I know the format is strange but it will make things easier
in the long run if all of the dates are the same when working between
the 2 different databases. Basically, I need to take a look at the
year portion (with a SUBSTRING function maybe) to see if it is greater
than 50 (there will not be any dates that are less than 1950) and if
it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the
month and day from the rest to form the date we need in NUMERIC(8).
So, a date of January 17, 2003 (currently in the format of 17/01/03)
would become 20030117. In VB, the function I would write is something
like the following:
/*
Dim sCurrentDate as String
Dim sMon as string
Dim sDay as String
Dim sYear as String
Dim sNewDate as String
sCurrentDate = "17/01/03"
sMon = Mid(sCurrentDat e, 4, 2)
sDay = Mid(sCurrentDat e, 1, 2)
sYear = Mid(sCurrentDat e, 7, 2)
If sYear < 50 Then
sYear = "20" & sYear
ElseIf sYear > 50 Then
sYear = "19" & sYear
End if
sNewDate = sYear & sMon & sDay
*/
I was thinking of doing this in a Stored Procedure but am really rusty
with SQL (it's been since college).
The datatype would end up being NUMERIC(8). How I would write it if I
new how to write it would be: grab the column name prior to the
procedure, create a temp column, format the values, place them into
the temp column, delete the old column, and then rename the temp
column to the name of the column that I grabbed in the beginning of
the procedure. Most likely this is the only way to do it but I have no
idea how to go about it. 1 4401 mw*******@quinn pumps.com (Milo Woodward) wrote in message news:<16******* *************** ****@posting.go ogle.com>... I have some columns of data in SQL server that are of NVARCHAR(420) format but they are dates. The dates are in DD/MM/YY format. I want to be able to convert them to our accounting system format which is YYYYMMDD. I know the format is strange but it will make things easier in the long run if all of the dates are the same when working between the 2 different databases. Basically, I need to take a look at the year portion (with a SUBSTRING function maybe) to see if it is greater than 50 (there will not be any dates that are less than 1950) and if it is concatenate 19 with it (ex. 65 = 1965). Then, concatenate the month and day from the rest to form the date we need in NUMERIC(8). So, a date of January 17, 2003 (currently in the format of 17/01/03) would become 20030117. In VB, the function I would write is something like the following: /* Dim sCurrentDate as String Dim sMon as string Dim sDay as String Dim sYear as String Dim sNewDate as String
sCurrentDate = "17/01/03" sMon = Mid(sCurrentDat e, 4, 2) sDay = Mid(sCurrentDat e, 1, 2) sYear = Mid(sCurrentDat e, 7, 2)
If sYear < 50 Then sYear = "20" & sYear ElseIf sYear > 50 Then sYear = "19" & sYear End if sNewDate = sYear & sMon & sDay */ I was thinking of doing this in a Stored Procedure but am really rusty with SQL (it's been since college).
The datatype would end up being NUMERIC(8). How I would write it if I new how to write it would be: grab the column name prior to the procedure, create a temp column, format the values, place them into the temp column, delete the old column, and then rename the temp column to the name of the column that I grabbed in the beginning of the procedure. Most likely this is the only way to do it but I have no idea how to go about it.
I strongly suggest that you rethink your approach, and change the
column to datetime. You can then do date calculations using the
standard functions (DATEADD etc.), compare the values to datetime
variables without conversion, etc. You can use CONVERT() to extract
dates in a particular format for passing to other systems.
Using numeric will give you serious problems in the long run, although
I appreciate that you may have limited control over the data model.
But if you really have no option but to use numeric, then this should
work (assuming that as you said, all dates are 1950 or later):
update dbo.MyTable
set DateColumn = convert(char(8) , convert(datetim e, DateColumn, 3),
112)
alter table dbo.MyTable
alter column DateColumn numeric(8)
Simon This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Robert Stearns |
last post by:
Either I missed something, or ALTER TABLE does not have this
capability. Is there any way of doing it except
DROPping all constraints which mention this table,
EXPORTing the data,
DROPping the table,
reCREATEing the table without the 'NOT NULL property,
reCREATEing the INDEXes,
reloading the data,
redefining all of DROPped constraints
reCREATE the view which were marked inactive by the above.
|
by: Godzilla |
last post by:
Dear all,
I have a challenge in hand and am not too sure how to accomplish this
using stored procedure. I have a table containing about 3 fields, but I
need to reorder/renumber a field value every time there is an insert or
delete on the table. Below is the table structure:
tableID customerID pageID
===== ======== ======
0 1 0
|
by: Fir5tSight |
last post by:
Hi All,
This seems to be a difficult problem for me. Hope you can help me
out...
I have a program whose major part is a grid that displays several
columns from data obtained from a stored procedure. There is a dialog,
and if the user clicks on the "OK" button, an extra column should be
appended to the grid. BTW, a separate stored procedure is used to get
data including this extra column.
|
by: JimmyKoolPantz |
last post by:
IDE: Visual Studio 2005
Language: VB.NET
Fox Pro Driver Version: 9.0.0.3504
Problem: I currently have a problem altering a DBF file. I do not get
any syntax errors when running the program. However, after I alter the
table and open microsoft excel to look at any changes; I get the
following error: "This file is not in a recognizable format" If I do
open the file in excel it looks like its not formatted.
|
by: fniles |
last post by:
I am using VB.NET 2003 and SQL Server 2000.
I have a table with a datetime column type. When inserting into the table
for that column, I set it to Date.Now.ToString("T")
, which is something like "2:50:54 PM". But after the row is inserted and I
check the data in the database, the column data is set to "1/7/2007 2:50:04
PM" (notice today's date in front of the time). If I insert data directly
into the table in the Enterprise Manager, the...
| |
by: Fir5tSight |
last post by:
Hi,
I have a stored procedure that looks like the follows:
-------------------------------------------------------------------------------------
SELECT
ClientName AS 'Client Name',
Location,
ReportInstanceID
FROM
|
by: =?Utf-8?B?QmFidU1hbg==?= |
last post by:
Hi,
I have a GridView and a SqlDataSource controls on a page. The SqlDataSource
object uses stored procedures to do the CRUD operations. The DataSource has
three columns one of which - "Modified" of type DateTime - is hidden since it
should not be edited by a user. The system handles the update for this
column. So, I have hidden (Visible=false) this column on the grid. In order
to access the value in this field, I have created a...
|
by: jeoffh |
last post by:
Background:
I am trying to "merge" some attributes into an existing XML column in my MS SQL 2005 database. The general idea is that I have an XML column in a table and I would like to update/delete some values while leaving the other values alone. I am designing this database/table/column so maybe I could use attributes or elements/nodes, the choice is ultimately mine. The one constraint is that I have to allow for customized name/value pairs....
|
by: dbrewerton |
last post by:
Hello everyone, hope you are all doing fine. What I'm attempting to do and have been tearing my hair out over for the last few weeks is trying to update timestamps in my MySQL database when this data gets fed in by an XML data feed. The data comes in as a group of slices. The number of slices per feed could be different so I have to dynamically find a way to take my existing stored procedure and expand on it. The problem I have is the timestamps...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |