Help with data type conversion !!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with data type conversion !!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-05-09 : 10:54:36

Hi all,

I have 2 conversion related to same table.

a) Date:
I have a table called Table1. It has a field called HDate(nvarchar type). The records are like this;
HDate
H01042008
H01082008
H01102006
H01112008

Each records always start with an H charatcter, the remaining characters always represents date in the format of DDMMYYY.
I want to convert this format into the date format of YYYYMMDD. So once coverted, the record should be as follows;
HDateConverted
2008-04-01
2008-08-01
2006-10-01
2008-11-01

b) In the same table I have another field called NoID of nvarchar type. The records are like this;
NoID
00.
000
001
002
003
004
005
006
007
008
010

I want to convert it to Integer. Once converted the records for above example should be like this;
NoIDConverted
0
0
1
2
3
4
5
6
7
8
10

How can I solve both these conversions?..
Thanks a million for your qucik help.

Zee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 11:00:40

SELECT CAST(SUBSTRING(HDate,2,LEN(HDate)-1) AS datetime),
CAST(NoID AS int)
FROM YourTable

Also keep in mind that datetime field will still have a timepart of 00:00:00. This is default way in which SQL server stores datetime.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-09 : 11:20:54

Are you sure that will all work visakh?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-09 : 11:23:58

There are several ways; here's one...

declare @Table1 table (HDate nvarchar(10), NoID nvarchar(10))
insert @Table1
          select 'H01042008', '00.'
union all select 'H01082008', '000'
union all select 'H01102006', '001'
union all select 'H01112008', '002'

set dateformat dmy

SELECT
    CAST(STUFF(STUFF(SUBSTRING(HDate,2,LEN(HDate)-1), 5, 0, '/'), 3, 0, '/') AS DATETIME),
    CAST(REPLACE(NoID, '.', '') AS INT)
FROM @Table1

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-05-09 : 11:51:20

No visakh16, it didn't work and gives the error "Arithmetic overflow error converting expression to data type datetime."
Instead I used the following which worked,

cast(left(substring(fieldname, 2,8),2) + '/' + substring(substring(fieldname, 2,8), 3, 2) + '/' + substring(substring(fieldname, 3,8), 4,4) as datetime)

Thank you all for your time and help.

Zee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 12:11:41

quote:
Originally posted by RyanRandall

Are you sure that will all work visakh?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Ah sorry i was in a bit of hurry on this one

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 12:12:24

quote:
Originally posted by zeeshan13

No visakh16, it didn't work and gives the error "Arithmetic overflow error converting expression to data type datetime."
Instead I used the following which worked,

cast(left(substring(fieldname, 2,8),2) + '/' + substring(substring(fieldname, 2,8), 3, 2) + '/' + substring(substring(fieldname, 3,8), 4,4) as datetime)

Thank you all for your time and help.

Zee

Sorry Zeeshan that wont work. You can use Ryan's aolution.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-05-09 : 12:20:33

How come? It worked for me....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 12:21:26

quote:
Originally posted by zeeshan13

How come? It worked for me....

I was telling about my soln

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-09 : 12:22:38

quote:
Sorry Zeeshan that wont work.

Well it will work, it's just not very elegant. This is a more elegant way using that approach...

SELECT CAST(SUBSTRING(HDate, 6, 4) + SUBSTRING(HDate, 4, 2) + SUBSTRING(HDate, 2, 2) AS DATETIME)
FROM @Table1

Zee - substring(substring(fieldname, 2,8), 3, 2) is the same as substring(fieldname, 4, 2)

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-09 : 13:46:11

Do you want to parse the date out of the HDate field to:
1) Create another varchar field that contains a date string? (if so why?)
2) Or, create a new field with an actual DATETIME datatype?

Here is another way (also very similar to Ryan's)

SET DATEFORMAT DMY
GO
DECLARE @Yak TABLE (HDate VARCHAR(9))

INSERT @Yak
SELECT 'H01042008'
UNION ALL SELECT 'H01082008'
UNION ALL SELECT 'H01102006'
UNION ALL SELECT 'H01112008'

-- Create String
SELECT CONVERT(VARCHAR(10), CAST(RIGHT(STUFF(STUFF(HDate, 4, 0, '/'), 7, 0, '/'), 10) AS DATETIME), 121)
FROM @Yak

-- Create DATETIME
SELECT CAST(RIGHT(STUFF(STUFF(HDate, 4, 0, '/'), 7, 0, '/'), 10) AS DATETIME)
FROM @Yak

-- Create DATETIME (Should be faster on larger data sets)
SELECT DATEADD(MONTH,((RIGHT(HDate, 4)-1900)*12)+RIGHT(LEFT(HDate,5), 2)-1,RIGHT(LEFT(HDate, 3),2)-1)
FROM @Yak

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources