Query Analyzer - 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

Query Analyzer

Author

Topic

maximus007
Starting Member

18 Posts

Posted - 2006-03-29 : 11:21:21

Hi;

I would like to change the format of dates that is in colum using query analyzer. The name of the colum is StartD and this how they look: 3292006 I would like to insert the / / . Can someone show me how to accomplish it.

Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 11:40:12

what is the datatype of the column defined as? If these values represent dates why aren't they defined as either datetime or smalldatetime?

Be One with the Optimizer
TG

maximus007
Starting Member

18 Posts

Posted - 2006-03-29 : 11:47:23

The datatype is varchar. They were using the application to capture the date and removing the "//". Then updating the records throught the application . If change the data type to smalldatetime will I loose the present date or will it just format the the date that is presently there and add //?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 11:57:29

to maintain the current values you'd need to save them off as converted values, update the existing values to NULL, modify the column's datatype then update the table to the saved values.

We can help you with the code but first: I assume 3292006 is 3/29/2006 but what date is this: 1112006?
1/11/2006 or 11/1/2006

Be One with the Optimizer
TG

maximus007
Starting Member

18 Posts

Posted - 2006-03-29 : 12:02:24

it would be 1/11/2006

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 12:22:18

use
set dateformat mdy (yours is month day year, no?)

create table tempTable(column definitions, yourDateColumn datetime)
select columns, yourCharColumThatHoldDates
from OrginalTable

chek the data in the tempTable if it's ok, change the column datatype and do
insert into OrginalTable (columns)
select columns
from tempTable

drop tempTable

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 12:58:02

Spirit1, I don't think that works without a seperator character (mmddyyyy) rather than mm/dd/yyyy


create table #temp1 (rowid int, StartD varchar(15))
create table #temp2 (rowid int, StartD datetime)

insert #temp1
select 1, '3292006' 

set dateformat mdy
insert #temp2 (StartD)
select StartD from #temp1

select	*
from	#temp1 a
join	#temp2 b
		on b.rowid = a.rowid

drop table #temp1
drop table #temp2

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

>>it would be 1/11/2006
how does your program save 11/1/2006?

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 13:00:22

stupid dateformats...
well then i guess it's substringing ahead....

Go with the flow & have fun! Else fight the flow

Blog thingie: weblogs.sqlteam.com/mladenp

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-29 : 13:00:37

quote:
Originally posted by maximus007

it would be 1/11/2006

How would u get 11/1/2006 ?
as 11012006 ?
(In other words can u tell us how would u get 1st November 2006 and 11th january 2006)

And is ur date & year combined ==> 6 characters always and the month may be 1 or 2 depending on the month ?

Srinika

maximus007
Starting Member

18 Posts

Posted - 2006-03-29 : 13:54:38

The application capture the current date then it remove the bars and store it in the database. Therefore the information has been manipulated before it gets updated to the table. I have all ready change the information from the application side. Going forward each input is stored correctly with the "/ / ". the problem I am having is how to format or change the previous date that are stored without "//". I hope this makes sense.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 13:54:51

You still haven't answered my (and Srinika's) question about how 11/1/2006 is stored. if you simply removed the seperators there will be no way to differentiate between these dates (11/1/2006 and 1/11/2006) if leading zeros were not used.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources