CODEWITHSUNDEEP
sqlsql-server-2008
murdoqq
murdoqq

Reputation: 21

Converting data for whole table SQL

I'm newbie in SQL and have some questions:

How can I convert columns (in my table with more than 10 000 rows) in my SQL table (I'm using SQL Server 2008):

  1. First column is nvarchar (50) and containing different string values, for e.g. like 20131211142319 and it's a date and time - 2013/12/11 14:23:19. How can I convert this value into date & time and affect this on all rows in this column (more than 10 000).

  2. And also I have column with numbers, all this numbers start from # + number - e.g. #8339274. How can I delete character "#" before all numbers in all rows? Note, that numbers in this column have a different length, from 5 characters to 15 characters.

Thank you in advance.

Upvotes: 2

Views: 666

Answers (2)

JBond
JBond

Reputation: 3242

I couldn't find a more elegant solution for the datetime conversion but here you go:

1. DATETIME conversion

This assumes your value is always in the same format you specified:

Example code for you to run

DECLARE @Value VARCHAR(255) = '20131211142319'

SELECT CONVERT(DATETIME,LEFT(@Value,8) + SPACE(1) + STUFF(STUFF(STUFF(RIGHT(@Value,6), 1,     0, REPLICATE('0', 0)),3,0,':'),6,0,':'))

This splits the field into two sections, the DATE portion LEFT(@Value,8) and then the TIME STUFF(STUFF(STUFF(RIGHT(@Value,6), 1, 0, REPLICATE('0', 0)),3,0,':'),6,0,':'). The TIME portion is essentially just adding in the colon where applicable (see STUFF on MSDN)so that it returns a value such as:

20131211 14:23:19 This makes it applicable to directly convert to a DATETIME.

2. Removing the # from the numbers

Example code for you to run

DECLARE @ValueNumber VARCHAR(255) = '#8339274'

SELECT SUBSTRING(@ValueNumber,2,LEN(@ValueNumber))

The above statement will take your number and only return data from the 2nd value onwards, excluding the #. See SUBSTRING on MSDN

To make this run on your table, just replace my variable names in the SELECT statement with your column names.

Example using the above on columns in a table:

SELECT CONVERT(DATETIME,LEFT([DATECOLUMNNAME],8) + 
SPACE(1) + STUFF(STUFF(STUFF(RIGHT([DATECOLUMNNAME],6), 
1, 0,REPLICATE('0',0)),3,0,':'),6,0,':')) AS [Date],
SUBSTRING([NUMBERCOLUMNNAME],2,LEN([NUMBERCOLUMNNAME])) AS [Number]
FROM [TABLENAME]

Replace [DATECOLUMNNAME] with the name of the column that holds your datetime value. Replace the [NUMBERCOLUMNNAME] with the name of the column that holds your number with the #.

Then finally replace [TABLENAME] with your table name that contains those columns.

Upvotes: 1

vhadalgi
vhadalgi

Reputation: 7189

try this : below answer is also correct

    declare @a nvarchar(50)
    set @a='20131211142319'

    select cast(left(@a,4)+'/'+substring(@a,5,2)+'/'+substring(@a,7,2)+ ' '+ substring(@a,9,2)+':'+substring(@a,11,2)+':' +right(@a,2) as datetime)

output's this --2013-12-11 14:23:19.000    

    declare @a nvarchar(10)

    set @a='#1234567'
    select replace(@a,'#','')

outputs this--1234567

Upvotes: 0

Related Questions