String > Datetime conversation problem

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)

String > Datetime conversation problem

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2008-03-22 : 11:34:17

hi,
i have a simple code here but i always get same error.
my code is :

declare @date datetime
declare @dates varchar(30)
declare @day varchar(2)
declare @month varchar(2)
declare @year varchar(4)

set @date = (select dateadd(day,0,getdate()))
set @day = (select datepart(day,@date))
set @month = (select datepart(month,@date))
set @year = (select datepart(year,@date))
set @tarih = (select '''' + @year + '-' + @month + '-' + @day + ' 00:00:00.000''' )

if @month < 10
begin
set @month = '0' + @month
end
if @day < 10
begin
set @day = '0' + @day
end
-------------IT GIVES ERROR HERE
set @dateS = (select '''' + @year + '-' + @month + '-' + @day + ' 00:00:00.000''' )

---
well ,
when i try this >
set @date (which is datetime format ) = '2008-03-22 00:00:00.000'
it works(manually),
but i cant dynamically bind my string to datetime formatted field.

then i tried,
set @dates = (select '''' + @year + @month + @day + '''' )
set @date = (select convert(datetime,@dates)

and it DOESNT work too,
what to do ?

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2008-03-22 : 11:48:29

damn i fixed
'''' <<<< that character causing the error
wrong one > (select '''' + @year + '-' + @month + '-' + @day + ' 00:00:00.000''' )
right one > (select '' + @year + '-' + @month + '-' + @day + ' 00:00:00.000' )

f*ck =)

MS BLESS US

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-22 : 12:57:35

any particular reason you're not using built in datetime functions like dateadd?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-22 : 18:16:48

Don't use string manipulation and conversions to and from character data types to deal with dates. Use math. As Mladen says, use functions like DateAdd() and so on. And if you are storing a month or day, use a numeric data type like INT, not varchar.

Finally, SQL Server doesn't have some basic DateTime functions to make this easy, so here's a bunch of them for you with some examples on how to use them:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources