insert datetime to a datetime column

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)

insert datetime to a datetime column

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-07-01 : 03:07:51

i have a SP which recives a value of type DateTime.
the value it recived is : '2009-01-07 10:32:38'
(which is the 1st of july)
when i run a query :
select CONVERT(datetime,'2009-01-07 10:32:38',103)
the result is as i want : '2009-07-01 10:32:38'
but when i insert it to a datetime column i see it like i recive it in the first time and not in the converted way
how can i handle this?
thanks
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-01 : 03:12:14

The column change to string instead of datetime?
when u selecting it out convert it back to the format you prefer?

AMITOFO+AMEN+YA ALLAH Hope the query works

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-07-01 : 03:15:09

i dont understand you question
i first get : '2009-01-07 10:32:38'
when i query : select CONVERT(datetime,'2009-01-07 10:32:38',103) i get '2009-07-01 10:32:38' (and this what i insert into the db
but when i check the updated cell i see : '2009-01-07 10:32:38'

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-01 : 03:27:59

Erm...i wanna mean that change the column field type to string...so the record will be '2009-07-01 10:32:38'. But i think mr.peso or mr.tan or others have better idea

AMITOFO+AMEN+YA ALLAH Hope the query works

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 03:46:20

Yes. Do NOT use VARCHAR to store dates.

N 56°04'39.26"
E 12°55'05.63"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-07-01 : 03:48:40

i dont use varchar, i have already [datetime] column which i can't change now.
any idea?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 04:04:59

You should pass the date time to the stored procedure in ISO format YYYYMMDD HH:MM:SS

Since you have pass in '2009-01-07', SQL Server treat it as ISO YYYY-MM-DD so your datetime variable is actually stored with date January 1, 2009.

When you use convert(datetime, '2009-01-07', 103) you are telling converting the datetime using style 103 which is a format dd/mm/yyyy and it is converted to 1 July 2009 (Why ? i don't know. Ask Microsoft).

So when you assign the datetime variable to your column, it will be January 1, 2009.

To avoid confusion, pass the date in in ISO format YYYYMMDD and it will not go wrong.

run this and see


declare	@var1	datetime,
	@var2	datetime

select	@var1	= '2009-01-07',
	@var2	= '2009-07-01'

select	@var1, style103 = convert(varchar(50), @var1, 103), style106 = convert(varchar(50), @var1, 106) union all
select	@var2, style103 = convert(varchar(50), @var2, 103), style106 = convert(varchar(50), @var2, 106)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources