datetime, getdate - 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 2005 Forums

.NET Inside SQL Server (2005)

datetime, getdate

Author

Topic

boodito
Starting Member

6 Posts

Posted - 2007-04-10 : 10:30:32

Am using the getdate() function to insert dates to a table in a database, the date are inserted as for example '22/10/2006 03:05:56 AM'... I used to retrieve rows with specific dates using C# with a query like:

"SELECT * from log WHERE log_date BETWEEN '" + dtCurrentDate.String() + "' AND '" + DateTime.Now().ToString() + "'"

when I moved to VB .NET the same query returned no results, I thought this is coz VB .NET shows the same date as '10/22/2006 03:05:56 AM'
so I changed the formatting in the query but still no result

I tried use the same query in SQL Server 2005 Query Window
SELECT * FROM log WHERE log_date BETWEEN '22/10/2006 03:00:00 AM' AND '22/10/2006 04:00:00 AM'
still no result even the following query returned no result
SELECT * FROM log WHERE log_date = '22/10/2006 03:05:56 AM'
I tried to use the cast and convert functions but it couldn't convert the values between quotation to date

in the end i used the query: SELECT getdate() to see how dates are represented, the result was as for example
2006/10/22 03:05:56 AM

any help??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 10:34:23

How are you storing values in the LOG_DATE column?
Please answer you are using DATETIME and not VARCHAR...

Peter Larsson
Helsingborg, Sweden

boodito
Starting Member

6 Posts

Posted - 2007-04-11 : 05:05:22

am surely using DATETIME type
my problem will be resolved if i manage to store these dates in the format 'dd/MM/yyyy hh:mm:ss tt' without the milliseconds
well when i open the table, the dates are in the format above
but when i retrieve rows using a query the format is different:
'yyyy/MM/dd hh:mm:ss.xxx tt'
I used the ToString function in vb to swap the day and the year, but I couldn't add the milliseconds

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 05:23:11

With this you remove milliseconds. But the performance will suffer.

"SELECT * from log WHERE CAST(CONVERT(VARCHAR, log_date, 120) AS DATETIME) BETWEEN '" + dtCurrentDate.String() + "' AND '" + DateTime.Now().ToString() + "'"

Peter Larsson
Helsingborg, Sweden

boodito
Starting Member

6 Posts

Posted - 2007-04-11 : 05:45:11

mmm, in fact am working on a real time application and this query should be executed once each second, so that would be a lot of performance suffering
is there any other solution?
could I insert the date without the milliseconds? as dates for sure
or someway to have a vb datetime varialbe with milliseconds?

thank you

Abdallah Hmayed
Dubai, UAE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 05:53:03

Who is responsible for the LOG_DATE column?
The application is sending the date? Or some kind of audit mechanism in the database? Or a stored procedure?

Peter Larsson
Helsingborg, Sweden

boodito
Starting Member

6 Posts

Posted - 2007-04-11 : 06:06:13

a stored procedure is sending the dates to the column, it insert between 1 and 10 rows a second. and i just need to retrieve the cound of rows inserted every second (am using a timer control in vb)

Abdallah Hmayed
Dubai, UAE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 06:08:02

Then cut the milliseconds in the stored procedure! You can post the code for the SP here.
And afterwards, do a one-time operation to cut all milliseconds already stored in the table.

Peter Larsson
Helsingborg, Sweden

boodito
Starting Member

6 Posts

Posted - 2007-04-11 : 06:14:08

here's the procedure used to insert the rows

ALTER PROCEDURE [dbo].[sp_insert_call]
-- Add the parameters for the stored procedure here
@Number varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO call_log (cl_channel_id, cl_phone_number, cl_date)
values ('2', @Number, getdate())
END

do i have to use the cast/convert on insertion?

Abdallah Hmayed
Dubai, UAE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 06:23:17

Try this. If will very well handle 10 inserts per second.

ALTER PROCEDURE [dbo].[sp_insert_call] 
-- Add the parameters for the stored procedure here
@Number varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO call_log (cl_channel_id, cl_phone_number, cl_date)
select '2', @Number, CONVERT(VARCHAR, getdate(), 120)
END

Peter Larsson
Helsingborg, Sweden

boodito
Starting Member

6 Posts

Posted - 2007-04-11 : 06:31:33

what about AM/PM ?

Abdallah Hmayed
Dubai, UAE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 06:44:26

What about it?

Since LOG_DATE column is a DATETIME column, SQL Server will automatically convert the ISO DATE FORMAT YYYY-MM-DD HH:MM:SS
into the local collation setting of your database.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources