Dynamic SQL Date format - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SQL Date format

Author

Topic

swatib
Posting Yak Master

173 Posts

Posted - 2006-02-14 : 23:23:38

I'm writing a stored proc.

I have a dynamic sql statement where table name is dynamic.
I need to compare the table field tbdate with 2 date variable defined inthe SP.

I'm facing a problem in the same dynamic sql where the date variable need to be converted in the varchar format.

My query is like this-

SELECT @SQL = 'Insert into TempCallByCall Select * from CallByCall'+ REPLACE(CONVERT(char(12),Getdate(),103),'/','') + ' where acdtimestamp >=' + convert(varchar,@sdate) + ' and acdtimestamp <=' + convert(varchar,@tdate)
EXEC (@SQL)

But I'm getting diff format for @sdate, @tdate, & acdtimestamp field?

Please give me correct query

Njoy Life

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-14 : 23:31:48

acdtimestamp is a datetime ?

try

SELECT @SQL = 'Insert into TempCallByCall Select * from CallByCall' + 
                    REPLACE(CONVERT(char(12),Getdate(),103),'/','') + 
                    ' where acdtimestamp >= ''' + convert(varchar,@sdate,112) +
                    ''' and acdtimestamp <= ''' + 
                    convert(varchar,@tdate,112) ''''

----------------------------------
'KH'
everything that has a beginning has an end

swatib
Posting Yak Master

173 Posts

Posted - 2006-02-14 : 23:39:53

This SQL still gives syntax error. On removing extra quotes, it gives error as
"Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."

The date format which is there in DB is mm/dd/yyyy hh:mm:ss TT
Njoy Life

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-14 : 23:58:05

quote:
The date format which is there in DB is mm/dd/yyyy hh:mm:ss TT

What is the data type for acdtimestamp ? Are you using datetime data type or varchar ? What is the data type for @sdate, @tdate ?

Datetime in database is stored in it's internal format not mm/dd/yyyy or dd/mm/yyyy or whatsoever. What you see is basically how Query Analyser present the date & time.

----------------------------------
'KH'
everything that has a beginning has an end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-15 : 01:28:44

I think we have already suggested you NOT to have seperate tables to have each day's data.
Post the full procedure code with parameter declarations

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources