datetime from char - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

datetime from char

Author

Topic

sqlkid
Starting Member

41 Posts

Posted - 2009-04-03 : 13:00:29

I have a column with date in the following format eg : 71129,711 6 & so on ( ie, 29th Nov,07 and 6th November 07). It is currently entered as char(6). (I think there is a space before 7).If I try to change the data format to datetime, I get the foll message : Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

How do i convert the this data to
1)dd/mm/yy format
2)month date format as in Nov 29 in above example
3)month date year as in Nov 29 07
4)when i enter these kind of data should it be done as char and then changed to date time or shd i enter it as some other data format ?

Gigi
Starting Member

23 Posts

Posted - 2009-04-03 : 13:15:38

is the date column being populated via an application of is it populated by a flat file or something?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 13:16:06

1.SELECT CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + SUBSTRING(datefield,3,4),3) FROM Table
2.SELECT LEFT(CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + SUBSTRING(datefield,3,4),0),7) FROM Table
3.SELECT CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + SUBSTRING(datefield,3,4),0) FROM Table
4. its better always to use datetime as datatype for storing dates and using iso format yyyy-mm-dd for passing date values

sqlkid
Starting Member

41 Posts

Posted - 2009-04-03 : 13:25:59

quote:
Originally posted by visakh16

1.SELECT CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + SUBSTRING(datefield,3,4),3) FROM Table
2.SELECT LEFT(CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + SUBSTRING(datefield,3,4),0),7) FROM Table
3.SELECT CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + SUBSTRING(datefield,3,4),0) FROM Table
4. its better always to use datetime as datatype for storing dates and using iso format yyyy-mm-dd for passing date values

1,2,3) getting the foll error
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

4)but you cannot save it directly as datetime data - without doing some transformation - right ?

sqlkid
Starting Member

41 Posts

Posted - 2009-04-03 : 13:26:49

quote:
Originally posted by Gigi

is the date column being populated via an application of is it populated by a flat file or something?

copied from flat (txt) file

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 13:39:30

quote:
Originally posted by sqlkid

quote:
Originally posted by visakh16

1.SELECT CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + SUBSTRING(datefield,3,4),3) FROM Table
2.SELECT LEFT(CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + SUBSTRING(datefield,3,4),0),7) FROM Table
3.SELECT CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + SUBSTRING(datefield,3,4),0) FROM Table
4. its better always to use datetime as datatype for storing dates and using iso format yyyy-mm-dd for passing date values

1,2,3) getting the foll error
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

4)but you cannot save it directly as datetime data - without doing some transformation - right ?

are you sure you've space as first character? also will format be consistent always?
you can save it directly provided its in iso format

sqlkid
Starting Member

41 Posts

Posted - 2009-04-03 : 14:00:40

[/quote]
are you sure you've space as first character? also will format be consistent always?
you can save it directly provided its in iso format

[/quote]
i'm importing date from a file which has provided for 6 characters to denote this field -yymmdd - however, in yy actually the first zeros are missing. format is consistent. even in the case of date, for dates 1 -9, the zeros are replaced by spaces. ie, 2nd november 07 will be denoted as 711 2 & NOT 71102

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 14:05:28

oh..i see. then what you need is


1.SELECT CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + RIGHT('0' + SUBSTRING(datefield,3,2),2) + RIGHT('0' + SUBSTRING(datefield,5,2),2),3) FROM Table
2.SELECT LEFT(CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + RIGHT('0' + SUBSTRING(datefield,3,2),2) + RIGHT('0' + SUBSTRING(datefield,5,2),2),0),7) FROM Table
3.SELECT CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + RIGHT('0' + SUBSTRING(datefield,3,2),2) + RIGHT('0' + SUBSTRING(datefield,5,2),2),0) FROM Table

sqlkid
Starting Member

41 Posts

Posted - 2009-04-03 : 14:22:20

quote:

Originally posted by visakh16

oh..i see. then what you need is


1.SELECT CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + RIGHT('0' + SUBSTRING(datefield,3,2),2) + RIGHT('0' + SUBSTRING(datefield,5,2),2),3) FROM Table
2.SELECT LEFT(CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + RIGHT('0' + SUBSTRING(datefield,3,2),2) + RIGHT('0' + SUBSTRING(datefield,5,2),2),0),7) FROM Table
3.SELECT CONVERT(datetime,CASE WHEN LTRIM(LEFT(datefield,2)) > 50 THEN '19' ELSE '20' END + RIGHT('0' + LTRIM(LEFT(datefield,2)),2) + RIGHT('0' + SUBSTRING(datefield,3,2),2) + RIGHT('0' + SUBSTRING(datefield,5,2),2),0) FROM Table

getting the same error message as before. my datefield data is as below

71129
71024
71112
712 6
711 6
71121
71015
71023
712 7
71113
711 7
71122
71014
71022
711 4
71031
71123
712 4
71114
71021
711 5
71124
71029
71115
712 5
711 2
71125
71020
71028
712 2
71116
71019
71126
711 3
71027
71030
71117
712 3
71018
71110
71127
712 8
711 8
71026
71017
71118
711 1
71128
71111
71120
71025
711 9
71016
71119
712 1
71130

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-03 : 14:56:53

[code]select * into s from
(
select '71129' t union all
select '71024' union all
select '71112' union all
select '712 6' union all
select '711 6' union all
select '71121' union all
select '71015' union all
select '71023' union all
select '712 7' union all
select '71113' union all
select '711 7' union all
select '71122' union all
select '71014' union all
select '71022' union all
select '711 4' union all
select '71031' union all
select '71123' union all
select '712 4' union all
select '71114' union all
select '71021' union all
select '711 5' union all
select '71124' union all
select '71029' union all
select '71115' union all
select '712 5' union all
select '711 2' union all
select '71125' union all
select '71020' union all
select '71028' union all
select '712 2' union all
select '71116' union all
select '71019' union all
select '71126' union all
select '711 3' union all
select '71027' union all
select '71030' union all
select '71117' union all
select '712 3' union all
select '71018' union all
select '71110' union all
select '71127' union all
select '712 8' union all
select '711 8' union all
select '71026' union all
select '71017' union all
select '71118' union all
select '711 1' union all
select '71128' union all
select '71111' union all
select '71120' union all
select '71025' union all
select '711 9' union all
select '71016' union all
select '71119' union all
select '712 1' union all
select '71130'
)s[/code]

[code]select convert(varchar,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112),3) from s
select left(datename(month,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112)),3)+' '+datename(day,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112)) from s
select convert(varchar,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112),109) from s[/code]

sqlkid
Starting Member

41 Posts

Posted - 2009-04-03 : 23:32:56

quote:

Originally posted by sakets_2000

select * into s from
(
select '71129' t union all
select '71024' union all
select '71112' union all
select '712 6' union all
select '711 6' union all
select '71121' union all
select '71015' union all
select '71023' union all
select '712 7' union all
select '71113' union all
select '711 7' union all
select '71122' union all
select '71014' union all
select '71022' union all
select '711 4' union all
select '71031' union all
select '71123' union all
select '712 4' union all
select '71114' union all
select '71021' union all
select '711 5' union all
select '71124' union all
select '71029' union all
select '71115' union all
select '712 5' union all
select '711 2' union all
select '71125' union all
select '71020' union all
select '71028' union all
select '712 2' union all
select '71116' union all
select '71019' union all
select '71126' union all
select '711 3' union all
select '71027' union all
select '71030' union all
select '71117' union all
select '712 3' union all
select '71018' union all
select '71110' union all
select '71127' union all
select '712 8' union all
select '711 8' union all
select '71026' union all
select '71017' union all
select '71118' union all
select '711 1' union all
select '71128' union all
select '71111' union all
select '71120' union all
select '71025' union all
select '711 9' union all
select '71016' union all
select '71119' union all
select '712 1' union all
select '71130' 
)s

select convert(varchar,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112),3) from s
select left(datename(month,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112)),3)+' '+datename(day,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112)) from s
select convert(varchar,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112),109) from s

1)where do I specify the table name ?
2) what is s & t ?
3) The dates i mentioned are the distinct list - there are multiple entries of this in the table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-04 : 02:32:03

Use this proper_date function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

sqlkid
Starting Member

41 Posts

Posted - 2009-04-04 : 02:45:03

quote:
Originally posted by madhivanan

Use this proper_date function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

If i could understand all that is written in the link above, I wouldnt be asking questions in "new to sql forum" :)
all i am asking for is some clarifications for an earlier answer - not some more new answers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-04 : 03:04:43

quote:

Originally posted by sqlkid

quote:

Originally posted by sakets_2000

select * into s from
(
select '71129' t union all
select '71024' union all
select '71112' union all
select '712 6' union all
select '711 6' union all
select '71121' union all
select '71015' union all
select '71023' union all
select '712 7' union all
select '71113' union all
select '711 7' union all
select '71122' union all
select '71014' union all
select '71022' union all
select '711 4' union all
select '71031' union all
select '71123' union all
select '712 4' union all
select '71114' union all
select '71021' union all
select '711 5' union all
select '71124' union all
select '71029' union all
select '71115' union all
select '712 5' union all
select '711 2' union all
select '71125' union all
select '71020' union all
select '71028' union all
select '712 2' union all
select '71116' union all
select '71019' union all
select '71126' union all
select '711 3' union all
select '71027' union all
select '71030' union all
select '71117' union all
select '712 3' union all
select '71018' union all
select '71110' union all
select '71127' union all
select '712 8' union all
select '711 8' union all
select '71026' union all
select '71017' union all
select '71118' union all
select '711 1' union all
select '71128' union all
select '71111' union all
select '71120' union all
select '71025' union all
select '711 9' union all
select '71016' union all
select '71119' union all
select '712 1' union all
select '71130' 
)s

select convert(varchar,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112),3) from s
select left(datename(month,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112)),3)+' '+datename(day,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112)) from s
select convert(varchar,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112),109) from s

1)where do I specify the table name ?
2) what is s & t ?
3) The dates i mentioned are the distinct list - there are multiple entries of this in the table

1 Like this
select convert(varchar,convert(datetime,replace(rtrim(ltrim('200'+your_column)),' ','0'),112),109) from your_table

2 S is the table name and t is the column name

3 Refer 1 that will handle all cases

Madhivanan

Failing to plan is Planning to fail

sqlkid
Starting Member

41 Posts

Posted - 2009-04-04 : 05:25:18

quote:

Originally posted by madhivanan

quote:

Originally posted by sqlkid

quote:

Originally posted by sakets_2000

select * into s from
(
select '71129' t union all
select '71024' union all
select '71112' union all
select '712 6' union all
select '711 6' union all
select '71121' union all
select '71015' union all
select '71023' union all
select '712 7' union all
select '71113' union all
select '711 7' union all
select '71122' union all
select '71014' union all
select '71022' union all
select '711 4' union all
select '71031' union all
select '71123' union all
select '712 4' union all
select '71114' union all
select '71021' union all
select '711 5' union all
select '71124' union all
select '71029' union all
select '71115' union all
select '712 5' union all
select '711 2' union all
select '71125' union all
select '71020' union all
select '71028' union all
select '712 2' union all
select '71116' union all
select '71019' union all
select '71126' union all
select '711 3' union all
select '71027' union all
select '71030' union all
select '71117' union all
select '712 3' union all
select '71018' union all
select '71110' union all
select '71127' union all
select '712 8' union all
select '711 8' union all
select '71026' union all
select '71017' union all
select '71118' union all
select '711 1' union all
select '71128' union all
select '71111' union all
select '71120' union all
select '71025' union all
select '711 9' union all
select '71016' union all
select '71119' union all
select '712 1' union all
select '71130' 
)s

select convert(varchar,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112),3) from s
select left(datename(month,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112)),3)+' '+datename(day,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112)) from s
select convert(varchar,convert(datetime,replace(rtrim(ltrim('200'+t)),' ','0'),112),109) from s

1)where do I specify the table name ?
2) what is s & t ?
3) The dates i mentioned are the distinct list - there are multiple entries of this in the table

this gives me a new table with the 56 data points in the correct date format. I need to correct the format in the original table and 56 is just the distinct entries in the table. They are repeated so many times in the original table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-04 : 06:11:41

Refer point 1 of my above reply. Replace your_column with actual column name and your_table with actual table name

Madhivanan

Failing to plan is Planning to fail

sqlkid
Starting Member

41 Posts

Posted - 2009-04-04 : 08:35:09

quote:
Originally posted by madhivanan

Refer point 1 of my above reply. Replace your_column with actual column name and your_table with actual table name

Madhivanan

Failing to plan is Planning to fail

I am lost. If i try to execute your point 1 (without doing the preceding union all command - I get the following message

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string

Now if i try to do the union all command which lists the 56 data points & give my existing table name / column, I get the foll message

Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tabletest' in the database.

tabletest = my existing table name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-04 : 09:33:10

quote:
Originally posted by sqlkid

quote:
Originally posted by madhivanan

Refer point 1 of my above reply. Replace your_column with actual column name and your_table with actual table name

Madhivanan

Failing to plan is Planning to fail

I am lost. If i try to execute your point 1 (without doing the preceding union all command - I get the following message

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string

Now if i try to do the union all command which lists the 56 data points & give my existing table name / column, I get the foll message

Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tabletest' in the database.

tabletest = my existing table name

It means that you have some data in the table that cannot be converted to datetime.

See if you get any data when running this

select your_column from your_table
where isdate(replace(rtrim(ltrim('200'+your_column)),' ','0'))=0

Madhivanan

Failing to plan is Planning to fail

sqlkid
Starting Member

41 Posts

Posted - 2009-04-04 : 10:28:02

quote:
Originally posted by madhivanan

quote:
Originally posted by sqlkid

quote:
Originally posted by madhivanan

Refer point 1 of my above reply. Replace your_column with actual column name and your_table with actual table name

Madhivanan

Failing to plan is Planning to fail

I am lost. If i try to execute your point 1 (without doing the preceding union all command - I get the following message

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string

Now if i try to do the union all command which lists the 56 data points & give my existing table name / column, I get the foll message

Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tabletest' in the database.

tabletest = my existing table name

It means that you have some data in the table that cannot be converted to datetime.

See if you get any data when running this

select your_column from your_table
where isdate(replace(rtrim(ltrim('200'+your_column)),' ','0'))=0

Madhivanan

Failing to plan is Planning to fail

I am getting all the entries in the date column as output

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 04:04:32

quote:
Originally posted by sqlkid

quote:
Originally posted by madhivanan

quote:
Originally posted by sqlkid

quote:
Originally posted by madhivanan

Refer point 1 of my above reply. Replace your_column with actual column name and your_table with actual table name

Madhivanan

Failing to plan is Planning to fail

I am lost. If i try to execute your point 1 (without doing the preceding union all command - I get the following message

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string

Now if i try to do the union all command which lists the 56 data points & give my existing table name / column, I get the foll message

Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tabletest' in the database.

tabletest = my existing table name

It means that you have some data in the table that cannot be converted to datetime.

See if you get any data when running this

select your_column from your_table
where isdate(replace(rtrim(ltrim('200'+your_column)),' ','0'))=0

Madhivanan

Failing to plan is Planning to fail

I am getting all the entries in the date column as output

It means the data available are all in wrong format
or you may need to give more informations

See this query which has sample data you posted but it returns nothing

declare @s table(t char(6))
insert into @s
select * from
(
select '71129' t union all
select '71024' union all
select '71112' union all
select '712 6' union all
select '711 6' union all
select '71121' union all
select '71015' union all
select '71023' union all
select '712 7' union all
select '71113' union all
select '711 7' union all
select '71122' union all
select '71014' union all
select '71022' union all
select '711 4' union all
select '71031' union all
select '71123' union all
select '712 4' union all
select '71114' union all
select '71021' union all
select '711 5' union all
select '71124' union all
select '71029' union all
select '71115' union all
select '712 5' union all
select '711 2' union all
select '71125' union all
select '71020' union all
select '71028' union all
select '712 2' union all
select '71116' union all
select '71019' union all
select '71126' union all
select '711 3' union all
select '71027' union all
select '71030' union all
select '71117' union all
select '712 3' union all
select '71018' union all
select '71110' union all
select '71127' union all
select '712 8' union all
select '711 8' union all
select '71026' union all
select '71017' union all
select '71118' union all
select '711 1' union all
select '71128' union all
select '71111' union all
select '71120' union all
select '71025' union all
select '711 9' union all
select '71016' union all
select '71119' union all
select '712 1' union all
select '71130'
)s

select t from @s
where isdate(replace(rtrim(ltrim('200'+t)),' ','0'))=0

Then, I wonder how you are getting result

Madhivanan

Failing to plan is Planning to fail

sqlkid
Starting Member

41 Posts

Posted - 2009-04-06 : 06:38:14

i think i got lot of complicated answers to a simple problem:)

what ive done

UPDATE mytable SET date=replace(date, ' ', '0')

Then I am able to change the data type in design table to datetime

& changing it back to “char’ gets me the output as month date – eg – Oct 21

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources