May 27, 2010 at 2:05 am
Hi..
sorry guys actually i posted the wrong script..in the previous post...hergo my query.
in my table ther is RUN_DATE column which is getdate()
SELECT RECORD_COUNT.TABLE_NAME,RECORD_COUNT.ROW_COUNT,INSERT_COUNT.NO_OF_INSERTS,UPDATE_COUNT.NO_OF_UPDATES, getdate() as RUN_DATE
INTO TOTAL_RECORD_COUNT
after executing my SP in the RUN_DATE column, the datetime format is
27/05/2010 08:30:00 whic is time that i executed my SP
here i need to set the time to 27/05/2010 00:00:00,the reason is in case if executed the SP twice in a single day on diffrent times ther will be 2 sets of data in the table,but i need the data to overwrite insted of creating twice.
would you have any idea about this pls?
thanx
May 27, 2010 at 2:20 am
101 would give you the US MM/DD/YYYY format.
103 would give you the DD/MM/YYYY format you're asking about.
May 27, 2010 at 2:27 am
hi thanx for ur reply,
i posted the wrong query..
could you pls refer to my post again ,i changed now, whic i am actually trying to get.
thanx
May 27, 2010 at 2:35 am
Well, I can't do any testing because you haven't provided me with enough information. But I suspect what you want is something like : -
SELECT record_count.table_name,
record_count.row_count,
insert_count.no_of_inserts,
update_count.no_of_updates,
( CONVERT(VARCHAR, Getdate(), 103) ) AS run_date
INTO total_record_count
If not, please read this article[/url] on how to post questions that are easier to answer 🙂
May 27, 2010 at 2:42 am
Thanx for ur help.
i ll try using this script,one mor equesting.....in mytable design i given the RUN_DATE column datatype to datetime.
if i use 103 will it pick only date as dd/mm/yyyy 00:000:00 ?
thanx
May 27, 2010 at 3:19 am
satya.sakamuri (5/27/2010)
Thanx for ur help.i ll try using this script,one mor equesting.....in mytable design i given the RUN_DATE column datatype to datetime.
if i use 103 will it pick only date as dd/mm/yyyy 00:000:00 ?
thanx
The best way to explain is for you to run the below script I think.
DECLARE @testtable TABLE(
id INT,
thedate DATETIME)
INSERT INTO @testtable
VALUES (1,
Getdate())
INSERT INTO @testtable
VALUES (2,
Dateadd(dd, -1, Getdate()))
-- First, just SELECT the contents of the table
-- Note that 'thedate'is displayed as yyyy-mm-dd hh:mm:ss:ms
SELECT id,
thedate
FROM @testtable
-- Now we convert 'thedate' to varchar, which strips the time with 103
SELECT id,
CONVERT(VARCHAR, thedate, 103) AS thedate
FROM @testtable
-- Now we convert back to datetime, showing how SQL Server treats the
-- string when a time is no longer there.
SELECT id,
CONVERT(DATETIME, (CONVERT(VARCHAR, thedate, 103)), 103) AS thedate
FROM @testtable
-EDIT- Of course, if you are actually using SQL Server 2008, consider using the "Date" data type instead.
May 27, 2010 at 3:31 am
grt..thanx for ur help,
now i got clear idea on wat iam doing..
iam using sql 2005,
anyway..i sorted with ur help..thanx.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply