Sql-server – How to split Date column values in Access into 2 columns

ms accesssql server

I have a date column that stores values for begining to end date as:
070314-073114
So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114, any suggestions?

This database is still in Microsoft Access, and eventually after cleaning it up, we will move it to SQL Server.

Thanks in advance

Best Answer

ALTER TABLE table ADD COLUMN Start_Date TEXT(6)
ALTER TABLE table ADD COLUMN End_Date TEXT(6)

update table
set Start_Date = Left(column,6),
End_Date = Right(column,6)

Related Solutions

Ms-access – Microsoft Access constrain view by date in outer query

How about:

SELECT a.orderid,
       a.ocustomerid
FROM   orders AS a
       INNER JOIN (SELECT b.orderid,
                          b.ocustomerid
                          b.opromisedshipdate
                   FROM   orders AS b
                   ) AS c
       ON c.orderid = a.orderid 
WHERE  c.opromisedshipdate = a.opromisedshipdate

Ms-access – Separating a date column in Access into Month, Day and Year while keeping the new columns ordered by a seperate column(primary key)

Select FileNumber, 
    DateOpen as DayOpen, 
    Month(#DateOpen#) as MonthOpen, 
    Year(#DateOpen#) as YearOpen
From [file]
Order by FileNumber

I assume by day you just want to show the date you have from the day open field. Month(DateOpen) will give you the month number. You weren't specific about what you were looking for in this field, so I assume this is ok. Order By FileNumber ensures you maintain the order of the primary key field.

Best Answer

Related Solutions

Ms-access – Microsoft Access constrain view by date in outer query

Ms-access – Separating a date column in Access into Month, Day and Year while keeping the new columns ordered by a seperate column(primary key)

Related Question