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 resultI tried use the same query in SQL Server 2005 Query WindowSELECT * 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 resultSELECT * 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 datein the end i used the query: SELECT getdate() to see how dates are represented, the result was as for example2006/10/22 03:05:56 AMany 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 LarssonHelsingborg, Sweden |
 |
|
boodito
Starting Member
6 Posts |
Posted - 2007-04-11 : 05:05:22
|
am surely using DATETIME typemy problem will be resolved if i manage to store these dates in the format 'dd/MM/yyyy hh:mm:ss tt' without the millisecondswell when i open the table, the dates are in the format abovebut 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 LarssonHelsingborg, 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 sufferingis there any other solution?could I insert the date without the milliseconds? as dates for sureor someway to have a vb datetime varialbe with milliseconds?thank youAbdallah HmayedDubai, 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 LarssonHelsingborg, 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 HmayedDubai, 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 LarssonHelsingborg, Sweden |
 |
|
boodito
Starting Member
6 Posts |
Posted - 2007-04-11 : 06:14:08
|
here's the procedure used to insert the rowsALTER PROCEDURE [dbo].[sp_insert_call] -- Add the parameters for the stored procedure here @Number varchar(20)ASBEGIN -- 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())ENDdo i have to use the cast/convert on insertion?Abdallah HmayedDubai, 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)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereINSERT INTO call_log (cl_channel_id, cl_phone_number, cl_date)select '2', @Number, CONVERT(VARCHAR, getdate(), 120)END Peter LarssonHelsingborg, Sweden |
 |
|
boodito
Starting Member
6 Posts |
Posted - 2007-04-11 : 06:31:33
|
what about AM/PM ?Abdallah HmayedDubai, 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:SSinto the local collation setting of your database.Peter LarssonHelsingborg, Sweden |
 |
|
|