date time data type - 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

date time data type

Author

Topic

newbie007
Starting Member

37 Posts

Posted - 2009-03-25 : 07:23:37

1)I have 2 tables both of which have a datefield column.
In Table 1, the date is entered as (eg) 071114 - ie, 14th Nov 2007 & I am able to save it as a datetime data type.
However in Table 2, the date is entered as 71114 - ie, there is no zero to denote 07. In place of zero there is a space.I'm unable to save it as datetime. I get the following message

Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

How can I save it also as datetime format ?

2)I need to retrieve all data from Table2 where its datefield = datefield of Table 1. What query will get me this output ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-25 : 07:45:47

select cast(right('0'+col,6) as datetime) from your_table

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-25 : 07:46:31

1. UPDATE Table2 SET datecolumn=replace(datecolumn, ' ', '0')

2. SELECT T2.* FROM Table2 T2 INNER JOIN Table1 T1 ON T2.datecolumn=T1.datecolumn

Can you clarify "the date is entered as 71114...I'm unable to save it as datetime"? That kind of entry is not a date, and dates are not "saved" with spaces or leading zeros. Are your columns defined as datetime or varchar? Or something else?

newbie007
Starting Member

37 Posts

Posted - 2009-03-25 : 08:25:14

quote:
Originally posted by robvolk

1. UPDATE Table2 SET datecolumn=replace(datecolumn, ' ', '0')

2. SELECT T2.* FROM Table2 T2 INNER JOIN Table1 T1 ON T2.datecolumn=T1.datecolumn

Can you clarify "the date is entered as 71114...I'm unable to save it as datetime"? That kind of entry is not a date, and dates are not "saved" with spaces or leading zeros. Are your columns defined as datetime or varchar? Or something else?

I'm importing data which has date in the format i mentioned.when i try to change the data type (enterprise manager - design table)from default char to 'datetime' i get the error. I will try out these solutions
Thanx

newbie007
Starting Member

37 Posts

Posted - 2009-03-25 : 11:13:00

quote:
Originally posted by madhivanan

select cast(right('0'+col,6) as datetime) from your_table

Madhivanan

Failing to plan is Planning to fail

I'm getting the foll error

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

newbie007
Starting Member

37 Posts

Posted - 2009-03-25 : 11:16:44

quote:
Originally posted by newbie007

quote:
Originally posted by robvolk

1. UPDATE Table2 SET datecolumn=replace(datecolumn, ' ', '0')

Even after the query is executed, the date data is stored same as before (with no zeros). If i try to change the data type to date time in design-table, i get the foll error
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

2. SELECT T2.* FROM Table2 T2 INNER JOIN Table1 T1 ON T2.datecolumn=T1.datecolumn

Can you clarify "the date is entered as 71114...I'm unable to save it as datetime"? That kind of entry is not a date, and dates are not "saved" with spaces or leading zeros. Are your columns defined as datetime or varchar? Or something else?

I'm importing data which has date in the format i mentioned.when i try to change the data type (enterprise manager - design table)from default char to 'datetime' i get the error. I will try out these solutions
Thanx

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-25 : 11:20:15

If it has a leading space...u may want to try this

select cast(right('0'+ ltrim(rtrim(@d)),6) as datetime)

newbie007
Starting Member

37 Posts

Posted - 2009-03-25 : 11:36:57

quote:
Originally posted by vijayisonly

If it has a leading space...u may want to try this

select cast(right('0'+ ltrim(rtrim(@d)),6) as datetime)

getting foll mssg :
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@d'.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-25 : 12:16:10

@d was just a variable I used. Use your actual column name in there.

newbie007
Starting Member

37 Posts

Posted - 2009-03-25 : 12:31:03

quote:
Originally posted by vijayisonly

@d was just a variable I used. Use your actual column name in there.

I did try that also - getting fllwng mssg

Must declare the variable '@testdate'.
(testdate is my column name)
also where is the table name in this query?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-25 : 12:43:52

Please post the actual statement you're using.

newbie007
Starting Member

37 Posts

Posted - 2009-03-25 : 12:48:00

quote:
Originally posted by robvolk

Please post the actual statement you're using.

select cast(right('0'+ ltrim(rtrim(@testdate)),6) as datetime) from test

test = tablename

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 12:52:57

quote:
Originally posted by newbie007

quote:
Originally posted by robvolk

Please post the actual statement you're using.

select cast(right('0'+ ltrim(rtrim(@testdate)),6) as datetime) from test

test = tablename

if testdate is your table field you dont require @. @ symbol is used to denote variables or parameters in T-sql

newbie007
Starting Member

37 Posts

Posted - 2009-03-26 : 02:36:54

quote:
Originally posted by visakh16

quote:
Originally posted by newbie007

quote:
Originally posted by robvolk

Please post the actual statement you're using.

select cast(right('0'+ ltrim(rtrim(@testdate)),6) as datetime) from test

test = tablename

if testdate is your table field you dont require @. @ symbol is used to denote variables or parameters in T-sql

1) Im getting the output as (eg) 2007-10-14 00:00:00.000) Is it possible to get only the date part ? (bcos another table has date in that format and I need to retrieve data from that table subject to dates matching in both)

2) How can i get this data added as a column in my original table.

I tried the following but got <Null> in the revddate column

ALTER TABLE test ADD revddate

UPDATE test
SET revddate = select cast(right('0'+ ltrim(rtrim(testdate)),6) as datetime) from test

3)can u explain the syntax u gave - why right ? and whats ltrim,rtrim ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-26 : 08:20:33

1 What is the datatype of the column of other table? You can use DATEPART

2
UPDATE test
SET revddate = cast(right('0'+ ltrim(rtrim(testdate)),6) as datetime)

3 Refer SQL Server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources