dayly table update

hello,
i must dayly update a table in my database with the values of a CSV file
(~300000 entries)
example of the tabel (artNr ,productname ,price )
000001 monitor 234,66
000003 pc 699,44
....
245433 router 126,33
Now dayly the table-content is deleted and the csv-file is imported
Is it possible a better way - to update only the modified values and insert
the new.
How can this be done?
thanksOne recommendation could be
1. Create a staging table called get_bcp_h_daily_csv
2. Truncate the table
3. DTS the csv file into staging table
4. Write the first entry to a surrogate table called ot_su_daily_csv as in
a) below.
5. Write a sProc that incrementally loads what's in the surrogate table into
a lookup table called ot_lu_daily_csv for your database as in b) below:
6. Schedule a job to run this DTS Each day
7. Sorted
a)
INSERT INTO ot_su_daily_csv (ColName1, ColName2)
SELECT ColName1, ColName2
FROM get_bcp_h_daily_csv BCP
WHERE NOT EXISTS ( SELECT * FROM ot_su_daily_csv SURR
WHERE SURR.Col1= BCP.Col1 )
b.)
INSERT INTO ot_lu_daily_csv
(Col1, Col2)
SELECT Col1, Col2
FROM ot_su_daily_csv SURR(nolock)
ORDER BY Col1|||thanks for the recommendation - it works well if only each day new values in
the csv-file are attached.
But in my csv file some colums of the articles are changed - like in the
example
example: - day1
000001 monitor 234,66
000003 pc 699,44
the next day - day 2
000001 monitor 230,03 (price is modified...)
000003 pc-3,4GHz 699,44 (product description is modified)
245433 router 126,33 -> ok will be detected and updated
....
how to make a correct update in this situation ...
thanks
Xavier|||On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:

>thanks for the recommendation - it works well if only each day new values i
n
>the csv-file are attached.
>But in my csv file some colums of the articles are changed - like in the
>example
>example: - day1
>000001 monitor 234,66
>000003 pc 699,44
>the next day - day 2
>000001 monitor 230,03 (price is modified...)
>000003 pc-3,4GHz 699,44 (product description is modified)
>245433 router 126,33 -> ok will be detected and updated
>....
>how to make a correct update in this situation ...
>thanks
>Xavier
Hi Xavier,
Load the new data in a staging table. Then run a procedure that updates
existing data and adds new data, as follows:
UPDATE t
SET Descr = s.Descr,
Price = s.Price,
.. (other columns)
FROM TheTable AS t
INNER JOIN StagingTable AS s
ON s.KeyColumn = theTable.keyColumn
WHERE t.Descr <> s.Descr
OR t.Price <> s.Price
OR ... (other columns)
INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
SELECT KeyColumn, Descr, Price, ... (other columns)
FROM Stagins AS s
WHERE NOT EXISTS
(SELECT *
FROM TheTable AS t
WHERE t.KeyColumn = s.KeyColumn)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks,
Xavier
"Hugo Kornelis" wrote:

> On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:
>
> Hi Xavier,
> Load the new data in a staging table. Then run a procedure that updates
> existing data and adds new data, as follows:
> UPDATE t
> SET Descr = s.Descr,
> Price = s.Price,
> ... (other columns)
> FROM TheTable AS t
> INNER JOIN StagingTable AS s
> ON s.KeyColumn = theTable.keyColumn
> WHERE t.Descr <> s.Descr
> OR t.Price <> s.Price
> OR ... (other columns)
> INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
> SELECT KeyColumn, Descr, Price, ... (other columns)
> FROM Stagins AS s
> WHERE NOT EXISTS
> (SELECT *
> FROM TheTable AS t
> WHERE t.KeyColumn = s.KeyColumn)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

datetime to smalldatetime

I need to convert a datetime field to smalldatetime.

This particular field we only care about the time portion (an example would be '1899-12-30 13:15:00.000')

For now I created another field say 'newTime' that is smalldatetime, in which I want to "update" to the smalldatetime version of the data. I know this will truncate the ms, but I don't care about that. Also the min date that can be used with smalldatetime is Jan 1 1900.

Not sure how to go about doing this.

you could use the CONVERT function. check out BOL for CONVERT functions.

sample:

SELECTCONVERT(varchar,getdate(), 101)

|||

So lets say I have 2 fields, "oldTime" and "newTime"

oldTime is a datetime data type

newTime is a smalldatetime data type

I want to run a query like

update myTableset newTime = oldTimewhere ...etc...

I get this error

Msg 298, Level 16, State 1, Line 1

The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.

Then I ran:

update myTable
set newTime =
(SELECTCONVERT(varchar, oldTime, 101))

I get the error:

Msg 296, Level 16, State 3, Line 1

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

The statement has been terminated.

I'm sure this is because of the date portion in oldTime is < 1900

Would dateadd(dd, 1, oldTime) be be the best way to go about this?

|||

you are probably better off using a VARCHAR instead of smalldatetime. If you have to use smalldatetime, then you cannot put in values with YEAR < 1900. Or you could use one of the CONVERT functions to put only the time part.

for example:

DECLARE @.tsmalldatetime, @.t2datetimeSET @.t2 ='Jul 19 1800 1:14PM'SET @.t =convert(varchar,@.t2,114)PRINT @.t

DateTime Ranges

Hi..
I am facing a problem trying to determine whether a point in tie falls within a specific date and time range.
Here is an example..
Is 7/20/2007 1:23:45PM in the range between (Thursday 8:00 PM) To (Sunday 7:59 AM)
ThanksI've not got Crystal on this PC, so excuse any errors, but I'd expect you could do something like

numbervar d := dayofweek({date}); //or whatever the 'get day' function is!
timevar t := ctime({date}); //Get just the time part

//return whether between Thursday 8pm and Sunday 8pm
(d = CrThursday and t >= ctime(20, 0, 0))
or d = CrFriday
or d = CrSaturday
or (d = CrSunday and t < ctime(20, 0, 0))|||Thanks my friend,

I used your CRsyntax and converted it to Basic as follows:

Dim d As number
Dim t AS time

d= dayofweek(currentdatetime)
t= ctime(currentdatetime)

'return whether between Thursday 8pm and Sunday 8pm
IF (d = CrThursday and t >= ctime(20, 0, 0)) or d = CrFriday or d = CrSaturday or (d = CrSunday and t < ctime(8, 0, 0)) THEN
FORMULA= "Code if True"
Else
FORMULA= "Code if False"
END IF

datetime in in sql query

Hi

I am trying to write a query involve parameters. For example, the query:

Select * from myTable

wheremyDateTime=@.dt;

If I run the query, I was asked to enter value for the parameter. The query can be generated, however I can't save it, the error message says: Must declare the variable @.dt. When I tried to declare it, the system doesn't support it. I am using SQL Server Managerment Studio 2005.

I also tried the query without the parameter:

Select * from myTable

wheremyDateTime=31/07/2007;

But it didn't return record for any datetime format.

Could anyone help please? I just want to get some records filtered by a certain DateTime.

Claire

Are you trying to bulit it as a view or a stored procedure? Its not possible to create a View with paramters.

Stored Proc would look like:

CREATEPROCEDURE sp_MyStoredProc
@.dtasDateTime
AS

BEGIN

SELECT
*
FROM
myTable
WHERE
myDateTime=@.dt

END

To run it you wold have to execute it:

exec sp_MyStoredProc GetDate()

|||

Hi,

You will have to check how are the dates stored in your column. If they are stored as MM/dd/yyyy hh:mm:ss AMPM then you will have to use a Convert function as shown at the end of this post

For your first query, you will need to declare your variable using this

Declare @.dt datetime

Select * from myTable

wheremyDateTime=@.dt;

For your second query, if only the date is stored then

Select * from myTable

wheremyDateTime='31/07/2007'

To understand this better, try these

selectgetdate()

SELECTDATEADD(dd, 0,DATEDIFF(dd, 0,GETDATE()))

SELECTCONVERT(VARCHAR(10),GETDATE(),111)

Check this link

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

Datetime format for dimension Please help me

Dear All,

How can I format a dimesion datetime column . for example while browsing a cube, the value of the dimension column 'DateOrder' shows like that 2002-11-01 00:00:00. I wan to get 01/11/2002 dd/mm/yyyy.

What I have to do. When I changed their cell value property as dd/mm/yyyy it doesnpt working ..Please to crrect my problem

with regards

Polachah

You could create a named calculation in the DSV which formats the Date column however you like and the use this as the name of the attribute.|||

Thank for replying my requirement

I did the same way but the format is not changed.. Also I tried to use that cube in a pivot grid table and tried to change the format there. Still the format is shown as yyyy/mm/dd like that..

|||

You can either break the date into pieces and join it back together however you want

Code Snippet

datename(dd,DateOrder) + '/' + convert(varchar,month(DateOrder)) + '/' + datename(yyyy,DateOrder)

But you would have to do a bit more work on the above code to get it producing a leading "0" on the day and month.

Or you can use the third parameter of the convert function that is used when converting from a datetime to a string (which I prefer to use if I can)

Code Snippet

convert(varchar,DateOrder,103)

Format 103 is dd/mm/yyyy - Books Online has a list of all the format numbers in the help for the CONVERT() function|||

Dear sir

Thank you very mcuh ... for your help .. I got from your advice what I need thank u verymuch again

Datetime format

Hi all,
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' format
How do i customize this format?
For example i want the value like 'ddmonyyyy hh:mm'
Help required.
Thanx in anticipation.
'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA

>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>

Datetime format

Hi all,
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' format
How do i customize this format?
For example i want the value like 'ddmonyyyy hh:mm'
Help required.
Thanx in anticipation.'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA

>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>

Datetime format

Hi all
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' forma
How do i customize this format
For example i want the value like 'ddmonyyyy hh:mm
Help required
Thanx in anticipation.'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
--
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA
>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>

Datetime Column Problem

Hi All,
Here is my situation:
I have a table which has a column of type datetime and it carries data
with timestamp in it. For example: 2004-08-16 16:09:56.120
I have another column which is also of type datetime but contains data
with no time values (because someone didn't pay much attention). For
example: 2004-08-16 00:00:00.000
Here is my problem:
I have about 50 stored procs where these columns are compared for
example: subj_svd_visit_date < subj_budget_start_date etc.
What is the best way to approach this problem so that I don't have to
make change in the 50 procs.
Just to note that in some places people are using GetDate() when they
are inserting values into these columns.
Thanks very much for your input.
*** Sent via Developersdex http://www.examnotes.net ***You can start here:
http://www.karaszi.com/sqlserver/info_datetime.asp
-oj
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:eHG$nxVQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> Here is my situation:
> I have a table which has a column of type datetime and it carries data
> with timestamp in it. For example: 2004-08-16 16:09:56.120
> I have another column which is also of type datetime but contains data
> with no time values (because someone didn't pay much attention). For
> example: 2004-08-16 00:00:00.000
> Here is my problem:
> I have about 50 stored procs where these columns are compared for
> example: subj_svd_visit_date < subj_budget_start_date etc.
> What is the best way to approach this problem so that I don't have to
> make change in the 50 procs.
> Just to note that in some places people are using GetDate() when they
> are inserting values into these columns.
> Thanks very much for your input.
> *** Sent via Developersdex http://www.examnotes.net ***|||I don't see the problem. This is still a valid datetime value: 2004-08-16
00:00:00.000. It simply has a time of midnight. All comparisons and such
are still very much valid against that value.
Andrew J. Kelly SQL MVP
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:eHG$nxVQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> Here is my situation:
> I have a table which has a column of type datetime and it carries data
> with timestamp in it. For example: 2004-08-16 16:09:56.120
> I have another column which is also of type datetime but contains data
> with no time values (because someone didn't pay much attention). For
> example: 2004-08-16 00:00:00.000
> Here is my problem:
> I have about 50 stored procs where these columns are compared for
> example: subj_svd_visit_date < subj_budget_start_date etc.
> What is the best way to approach this problem so that I don't have to
> make change in the 50 procs.
> Just to note that in some places people are using GetDate() when they
> are inserting values into these columns.
> Thanks very much for your input.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks oj for the link, it is very helpful.
Thanks Kelly for looking into the problem. You are right that there is
no problem on the surface but the time that one of the field is storing
is not needed. That is what my question was. Given that now one of the
field stores time value that is not needed, what do I do to get rid off
it and what do I do to the code that compares it.
*** Sent via Developersdex http://www.examnotes.net ***|||Not sure I understand what you are asking. The Link OJ posted should answer
most questions about using datetime. If you are asking how to make all
datetime values store midnight and retain the date portion you can do
something like this:
SELECT CONVERT(DATETIME,CONVERT(VARCHAR(8),Your
DateTimeCol,112))
Andrew J. Kelly SQL MVP
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:ezgs55ZQFHA.244@.TK2MSFTNGP12.phx.gbl...
> Thanks oj for the link, it is very helpful.
> Thanks Kelly for looking into the problem. You are right that there is
> no problem on the surface but the time that one of the field is storing
> is not needed. That is what my question was. Given that now one of the
> field stores time value that is not needed, what do I do to get rid off
> it and what do I do to the code that compares it.
>
> *** Sent via Developersdex http://www.examnotes.net ***

Datetime and null value

In my stored procedure i'm extracting datevalues from a table and printing t
hem
in yyyy-mm-dd hh:mi:ss format.
for example if the table value is 'Jul 16 2004 12:00AM' then my statement
(which is dynamically generated)
select Convert(CHAR(20),cast('Jul 16 2004 12:00AM ' as datetime),20)
will print " 2004-07-16 00:00:00 "
but if the table's datevalue is null then the statement
select Convert(CHAR(20),cast(' ' as datetime),20)
is printing "1900-01-01 00:00:00 "
I want the second one to be blank value(' ') what should I do?
Thanks
Chandra
Declare @.t datetime
set @.t='2005-07-16 12:00:00'
select case when @.t is null then convert(varchar,'',101) else
Convert(CHAR(20),cast(@.t as datetime),20) end
set @.t=null
select case when @.t is null then convert(varchar,'',101) else
Convert(CHAR(20),cast(@.t as datetime),20) end
Madhivanan|||Actually I mentioned that it is dynamically generated statement
like the following
select 'insert into employee (hire_date) values ( Convert(CHAR(20),cast('''+
isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
will give you an insert statement.
this insert statement when run, will insert the data into table.
at this point I'm having the problem as the insert statement is inserting
default date(1900...) for empty strings(actually null values)
thanks
chandra
"Madhivanan" wrote:

>
> Declare @.t datetime
> set @.t='2005-07-16 12:00:00'
> select case when @.t is null then convert(varchar,'',101) else
> Convert(CHAR(20),cast(@.t as datetime),20) end
> set @.t=null
> select case when @.t is null then convert(varchar,'',101) else
> Convert(CHAR(20),cast(@.t as datetime),20) end
>
> Madhivanan
>|||Chandra
declare @.dt datetime
set @.dt =''
select @.dt
--1900-01-01 00:00:00.000
select case when @.dt ='' then null else @.dt end as d
--NULL
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:FEA86C39-4E3D-4D66-9588-1421CB53A918@.microsoft.com...
> Actually I mentioned that it is dynamically generated statement
> like the following
> select 'insert into employee (hire_date) values (
> Convert(CHAR(20),cast('''+
> isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
> will give you an insert statement.
> this insert statement when run, will insert the data into table.
> at this point I'm having the problem as the insert statement is inserting
> default date(1900...) for empty strings(actually null values)
> thanks
> chandra
>
> "Madhivanan" wrote:
>|||> Actually I mentioned that it is dynamically generated statement
> like the following
> select 'insert into employee (hire_date) values (
> Convert(CHAR(20),cast('''+
> isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
> will give you an insert statement.
> this insert statement when run, will insert the data into table.
> at this point I'm having the problem as the insert statement is inserting
> default date(1900...) for empty strings(actually null values)
Can you tell us what you WANT to insert when the Hire_date is NULL?
I'll make a guess:
SELECT 'INSERT employee (hire_date)
SELECT '+COALESCE(CONVERT(VARCHAR(8), Hire_date, 112), 'NULL')
FROM employee|||> SELECT 'INSERT employee (hire_date)
> SELECT '+COALESCE(CONVERT(VARCHAR(8), Hire_date, 112), 'NULL')
> FROM employee
Whoops, should be:
SELECT 'INSERT employee (hire_date)
SELECT '+COALESCE(''''+CONVERT(VARCHAR(8), Hire_date, 112)+'''', 'NULL')
FROM employee

DateTime

Hello All

Here is the Question

I have created a table in order to time my query in parts:

For example

/*********************************************************************************/

USE [PUBS]

GO

CREATE TABLE [dbo].[IntegrationTestTime]

(

[Process] [varchar] (30),

[StartTime] datetime,

[EndTime] datetime,

[RunTime] datetime,

[DataRetrieveTime] datetime,

) ON [PRIMARY]

GO

INSERT INTO IntegrationTestTime (ProcessName) VALUES ('Change1');

INSERT INTO IntegrationTestTime (ProcessName) VALUES ('Change2')

/*********************************************************************************/

During the start of the query I am updating the StartTime Col for Change1 with Getdate()

UPDATE IntegrationTestTime SET StartTime = GETDATE() where Process = 'Change1'

At the End of the query i am updating EndTime Col for Change1 with Getdate()

UPDATE IntegrationTestTime SET EndTime = GETDATE() where Process = 'Change1'

Q1) I want to update RunTime Col with the time difference upto milli second from StartTime to EndTime col.

i.e, Runtime Col must have the exact time it took to execute the whole query

UPDATE IntegrationTestTime SET RunTime = (SELECT datediff(MS, StartTime, EndTime)

where Process = 'Change1')

But this gives me an error, i mean wrong value is being updated

Q2) In middle of the query, after executing certain SQL commands, I want to time it again and set [DataRetrieveTime] Colunm with the difference of StartTime and Getdate time at that instance.

Any suggestion please?

I would use a computed field, try this DDL for your table.

Code Snippet

CREATE TABLE [dbo].[IntegrationTestTime](

[Process] [nvarchar](50) NULL,

[StartTime] [datetime] NULL,

[EndTime] [datetime] NULL,

[RunTime] AS (datediff(millisecond,[StartTime],[EndTime])) PERSISTED,

[DataRetrieveTime] datetime

) ON [PRIMARY]

GO

|||

Well lets see.

Check the data types of the columns and the values you are trying to store in them. You have defined RunTime as datetime and you are trying to store the milli seconds in them. To store the milli seconds you don't need a datetime column.

|||

I am want to store the time difference between the two time (Start and End Time). Its hould be upto an accuracy of milli second

Suppose Start tiem is 4:55:56.981

and ENd time is 5: 01:57.991

so the RunTime = 00: 06 : 01: 010

This is what i need

|||You need to understand how datetime works. Datetime uses 8 bytes to store the datetime and it uses first 4 bytes to store the date and next four to store the time. You can't separate the values. Your code is trying to store the milli seconds in a datetime field which tries to convert implicitly to a date and time value. If you want to store only the difference of time between columns, you need a int data type (only 4 bytes) not a datetime datatype. Also don't try to store the formats of data in the database. let app layer handle the formatting of data.

|||

A could of things. 1. SQL Server will only give you .003 second accuracy in GetDate(). 2. Shawn's answer DOES store the difference, with no trigger or follow on code to muck with:

CREATE TABLE [dbo].[IntegrationTestTime](
[Process] [nvarchar](50) NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[RunTimeMs] AS (datediff(millisecond,[StartTime],[EndTime])) PERSISTED,
) ON [PRIMARY]

GO

INSERT INTO IntegrationTestTime (Process) VALUES ('Change1');
INSERT INTO IntegrationTestTime (Process) VALUES ('Change2')

GO
UPDATE IntegrationTestTime SET StartTime = GETDATE() where Process = 'Change1'

UPDATE IntegrationTestTime SET EndTime = GETDATE() where Process = 'Change1'

select *
from IntegrationTestTime

Process StartTime EndTime RunTimeMs

-- -- -- --
Change1 2007-09-06 14:03:37.777 2007-09-06 14:04:06.157 28380
Change2 NULL NULL NULL

Your update statement:

UPDATE IntegrationTestTime SET RunTime = (SELECT datediff(MS, StartTime, EndTime)

WHERE Process = 'Change1')

Won't work because you are trying to store it to a datetime value. In my example, (after changing RunTime to be an integer) the value returns: 1925-08-21 00:00:00.000. So the problem is likely that the date being returned is out of the range for your tool?

|||

I think i have a wrong code

Can any body suggest me whats the right code / procedure to get the difference between the Start and end.

I have started at so and so time

and ended at so and so time

whats the time taken to do taht job?

|||

This is the right code: datediff(MS, StartTime, EndTime). It is just that it is not a date value. It is a number of milliseconds difference. You can turn this into 00:00:00.000 easily enough, just by doing the math to peel off minutes, seconds, etc. Here is a blog I wrote on that subject: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1238.entry

You could store that formatted output into your table, likely as a varchar value.

Dates for previous week

Good morning all -
I need to run a report against the previous full w... for example,
today the report should run for dates from 6-18-2006 to 6-24-2006.
I have found code that will tell me the previous Saturday and could
just subtract 7 days from that, but I am wondering if there isn't some
more elegant way using the various datepart functions.
Thanks-
Daniellejust added here :)
http://omnibuzz-sql.blogspot.com/20...l.blogspot.com/|||Have you considered using a calendar table?
Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519
AMB
"wxbuff@.aol.com" wrote:

> Good morning all -
> I need to run a report against the previous full w... for example,
> today the report should run for dates from 6-18-2006 to 6-24-2006.
> I have found code that will tell me the previous Saturday and could
> just subtract 7 days from that, but I am wondering if there isn't some
> more elegant way using the various datepart functions.
> Thanks-
> Danielle
>|||Hi There
You may want to try this
declare @.d datetime
set @.d=getdate()
Select convert(varchar,dateadd(d,-6-datepart(dw,@.d),@.d),103),
convert(varchar,dateadd(d,-datepart(dw,@.d),@.d),103)
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Alejandro Mesa wrote:
> Have you considered using a calendar table?
> Why should I consider using an auxiliary calendar table?
> http://www.aspfaq.com/show.asp?id=2519
>
> AMB
> "wxbuff@.aol.com" wrote:
>

Dates - information entered 3 months ago

Hello All,
I need to create stored procedure that will output information created 3
months after the record was created. For example: if the stored procedure was
run today or based on a date parameter I would like it to output all records
created 3 months ago to that day. There are other parameters I need,but I
think I can take care of those,
Thanks in advance.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200804/1On Apr 29, 5:22=A0pm, "Jay via SQLMonster.com" <u7124@.uwe> wrote:
> Hello All,
> I need to create stored procedure that will output information created 3
> months after the record was created. For example: if the stored procedure =was
> run today or based on a date parameter I would like it to output all recor=ds
> created 3 months ago to that day. There are other parameters I need,but I
> think I can take care of those,
> Thanks in advance.
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx=
/sql-server-reporting/200804/1
In SQL try:
SET DATEPARAM =3D DATEADD(MONTH,-3,GETDATE())
In SSRS/VB try:
=3DDateAdd(DateInterval.Month, -3, Today())
HTH
toolman

Dates

I have a field where the date is in a number string, for example, 20020731. I need to convert this into a date string. Any ideas?
Thanks.Look up convert in the Holy Book (SQL Server Books Online)|||Several possibilities:
1. Write a scalar UDF that returns a date time from a set string format.

2. USe the following T-SQL (though it may be slow):

declare @.DateString varchar(8)

select @.DateString = '20030731'

select
cast(substring(@.DateString, 5, 2) + '/' + substring(@.DateString, 7, 2) + '/' + substring(@.DateString, 1, 4) as DateTime)

3. If you are importing this date into your database from another data source using DTS, you can use on of the Copy options to specify that the source is a date/time string (and then specify the precise format).

Regards,

Hugh Scott
Originally posted by exdter
I have a field where the date is in a number string, for example, 20020731. I need to convert this into a date string. Any ideas?
Thanks.|||Usually, CONVERT is used to transform a date/time into a char or varchar data type. Looking at it, I don't see anything that would immediately allow you to take a string an convert it to date/time.

Regards,

hmscott

Originally posted by Enigma
Look up convert in the Holy Book (SQL Server Books Online)|||Thats what I was afraid of. Orqacle makes it so easy.
Thanks for your time.|||I just re-read your sig line. I nearly spit coffee all over the keyboard. Thanks for starting my day off with a laugh.

:-)

Originally posted by Enigma
Look up convert in the Holy Book (SQL Server Books Online)|||Why? And I'm glad you were amused.|||Originally posted by hmscott
Usually, CONVERT is used to transform a date/time into a char or varchar data type. Looking at it, I don't see anything that would immediately allow you to take a string an convert it to date/time.

Regards,

hmscott

how about

select convert(varchar,convert(datetime,'20031201'),101)|||I need to put a column name in there. If I put select convert(char(10),column_name,101) from table_name, I just get the same string I had before.|||Originally posted by exdter
I need to put a column name in there. If I put select convert(char(10),column_name,101) from table_name, I just get the same string I had before.

Use

select convert(varchar(10),convert(datetime,column_name), 101)|||This is what I get:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.|||Enigma:
I stand corrected. I had never seen that before. It seems to only work if the data is formatted YYYYMMDD (or YYMMDD). Is that correct, or is there an option to specify the order of characters in the date string?

As for your sig, there's a classic definition of humor, that I can't recall right now, something to do with continuity and perception and cognition. Anyway, it met that definition.

Regards,

hmscott|||Try this:

declare @.DateString varchar(8)

select @.DateString = '20030731'

select convert(datetime, @.DateString)

Originally posted by hmscott
Enigma:
I stand corrected. I had never seen that before. It seems to only work if the data is formatted YYYYMMDD (or YYMMDD). Is that correct, or is there an option to specify the order of characters in the date string?

As for your sig, there's a classic definition of humor, that I can't recall right now, something to do with continuity and perception and cognition. Anyway, it met that definition.

Regards,

hmscott|||I'm not sure I follow you. The string I have is in number format and is in the 'yyyymmdd' format.|||I need to put a column name there.|||This is what I get:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

In case you are receiving that error , there is surely some value which does not fit in into the yyyymmdd format. You will need to correct tahat first.|||Sorry, try this:

/* begin DDL */
CREATE TABLE DateNumbers (
DateNumber int
)
GO

INSERT INTO DateNumbers VALUES (20030731)
GO
INSERT INTO DateNumbers VALUES (20030801)
GO
INSERT INTO DateNumbers VALUES (20030802)
GO

SELECT Cast(Cast(DateNumber as Varchar(8)) as datetime) FROM DateNumbers

I did not understand that the field was numeric.

Regards,

hmscott
Originally posted by exdter
I need to put a column name there.|||I have 15000 dates in the table. I need to use a column name.
Thanks|||Originally posted by hmscott
Enigma:
I stand corrected. I had never seen that before. It seems to only work if the data is formatted YYYYMMDD (or YYMMDD). Is that correct, or is there an option to specify the order of characters in the date string?

As for your sig, there's a classic definition of humor, that I can't recall right now, something to do with continuity and perception and cognition. Anyway, it met that definition.

Regards,

hmscott
from the Holy book again

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

the style values used converting datetime to varchar work the other way round too
eg : select convert(datetime,'12/01/2003',103)
style 103 : dd/mm/yy (British/French)|||If I use this select convert(varchar(10),convert(datetime,column_name), 101)
and put in the actual string, it works. If I try to put in the column name, I get the arithmetic error. I can't see why I could get this error. There are zeros and nulls in the table, but I do where column>0 and column is not like null|||It's because I mis-read your post the first time. You should use this function here (which casts the numeric to a string before passing it in to be cast as a datetime).

SELECT Cast(Cast(DateNumber as Varchar(8)) as datetime) FROM DateNumbers

Regards,
hmscott

Originally posted by exdter
If I use this select convert(varchar(10),convert(datetime,column_name), 101)
and put in the actual string, it works. If I try to put in the column name, I get the arithmetic error. I can't see why I could get this error. There are zeros and nulls in the table, but I do where column>0 and column is not like null|||Originally posted by exdter
If I use this select convert(varchar(10),convert(datetime,column_name), 101)
and put in the actual string, it works. If I try to put in the column name, I get the arithmetic error. I can't see why I could get this error. There are zeros and nulls in the table, but I do where column>0 and column is not like null

Well .. as i said before

quote:
------------------------

This is what I get:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

------------------------

In case you are receiving that error , there is surely some value which does not fit in into the yyyymmdd format. You will need to correct tahat first.|||I get this
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Type column_name is not a defined system type.
I used this SELECT Cast(Cast(column_name as Varchar(8)) as datetime) FROM table_name|||Originally posted by exdter
I get this
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Type column_name is not a defined system type.
I used this SELECT Cast(Cast(column_name as Varchar(8)) as datetime) FROM table_name

well lets see ...
try this

select * from your_table where ((substring(your_column,1,4) < '1753' or substring(your_column,1,4) < '9999' or substring(your_column,5,2) <'01' or substring(your_column,5,2) > '12' or substring(your_column,7,2) <'01' or substring(your_column,5,2) >'31' )

and see if you get any rows|||The column is in number form and substring works for char from.|||see if you can take a bcp out for the particular column and post it here so we can work on it|||Sorry, I don't know what bcp is.|||Run " select column_name from table_name" in Query analyzer.
Select the results ...
copy into text file and post here ..|||This is just a sample of the column. Is this ok?
20020731
19990423
19990607
19960903
19980402
20010718
19930419
20000101
19960329
19950109
20000630
19970815
20010118
20001205
19960306
19991116
19960313
19930719
19910502
20000509
20010926
20011106
20000517
19950525
19981029|||select convert(datetime, convert(varchar,20031120)) as xxx

You can replace 20031120 by the actual column name.

If you want it to be a string, you can further convert datetime into char.|||This works for this instance, but i need to do this for a whole column. If I put the column name, I get an error.
Thanks.|||exdter ...
we would need the complete data to point out where the error is ..|||I checked the data. I put a clause where column_name>0 and column_name is not null. The data that appears is all in the same format as what I posted. It goes 'yyyy/mm/dd'|||select * from your_table
where (
(substring(convert(varchar(8),your_column),1,4) < '1753'
or substring(convert(varchar(8),your_column),1,4) < '9999'
or substring(convert(varchar(8),your_column),5,2) <'01'
or substring(convert(varchar(8),your_column),5,2) > '12'
or substring(convert(varchar(8),your_column),7,2) <'01'
or substring(convert(varchar(8),your_column),7,2) >'31' )

Does this return any row ?|||Try this:

Select * from tablename where Isdate(cast(columnname as char(8))) = 0

That should help identify bad data.

blindman|||thanks blindman ...
that was exactly what i was searching for

time to get back to the holy book :)|||I put in my query 'where column_name>0'
Thanks for your help.|||If I put
set dateformat ymd
go
select cast(column_name as smalldatetime)
from table_name
where column_name =0
go

It works. But it only works on the zeros. Otherwise, if I put
and column_name>0 then I get the error:

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type smalldatetime.|||Select * from tablename where Isdate(cast(columnname as char(8))) = 0

does this return any results...?|||It does. Thats why in my convert query I added
where column_name>0|||OK, so what about

Select *
from tablename
where Isdate(cast(column_name as char(8))) = 0
and column_name>0

blindman|||I get no results. I did:

column_name=0
column_name is null
len(column_name) !=8

and got no results for any of them
I even physically went through and looked at the dates and they were all fine.|||Originally posted by exdter
I get no results. I did:

column_name=0
column_name is null
len(column_name) !=8

and got no results for any of them
I even physically went through and looked at the dates and they were all fine.

I also ran

select isdate(column_name)
from table_name

and got results that the column can be converted to a date.|||Wow .. this has become the biggest thread of all times ...43 posts !!!

exdter ... can you post the exact query you are running on your machine that is returning an error. Also can you give the result of the query

Select * from tablename where Isdate(cast(columnname as char(8))) = 0|||For the query:

Select * from tablename where Isdate(cast(columnname as char(8))) = 0 , I get no results. Meaning there are no zeros. I picked a different table where there are no zeros.|||Originally posted by exdter
For the query:

Select * from tablename where Isdate(cast(columnname as char(8))) = 0 , I get no results. Meaning there are no zeros. I picked a different table where there are no zeros.

When I run

select convert(char(10),column_name,101)
from table_name

I just get the same string back as what is already there. The numeric string. For example 20030705
I really appreciate your help on this.

I am also looking at

select left(column_name,4) + '/' + right(column_name,2) + '/' --+ right(column_name,3)
from table_name.

I got it to look like 2002/24/ so far.|||select convert(varchar(10),convert(datetime,column_name), 101)
from table_name where isdate(cast(columnname as char(8))) = 1|||Syntax error near 'as'|||Originally posted by exdter
Syntax error near 'as'
OOPS ...

select convert(varchar(10),convert(datetime,column_name), 101)
from table_name where isdate(convert(varchar(8),columnname)) = 1|||Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

And I know that all the strings can be converted because I ran
isdate(column_name) and got all 1's.|||Can you post the ddl for the table ?

Am running out of ideas :(|||Sorry, whats the ddl?|||I mean the SQL script for creating the table.|||Ya know...

CREATE TABLE myTable99 (Col1 int, ect...

AND Sample Data...

INSERT INTO myTable99(Col, ect..
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect

Would help us a lot...|||CREATE TABLE datestimes (firmfile varchar(20),orddate int(4))
AND Sample Data...

INSERT INTO datestimes(firmfile '03000004',orddate 20030724)

Thats all it is. There are about 15000 rows and all the firmfiles are just our folder numbers. And then there is the orddate which is the date the order was put in. All the orddate data is in the format 'yyyymmdd'
I checked this.
Is this enough?
Thanks alot.|||Try zeroing in on the problem:

select convert(datetime,cast(column_name as varchar(8)))
from table_name
where column_name between 19000101 and 20040101

This will give an error. So then try:
select convert(datetime,cast(column_name as varchar(8)))
from table_name
where column_name between 19900101 and 20040101

Still get the error? Try:
select convert(datetime,cast(column_name as varchar(8)))
from table_name
where column_name between 19950101 and 20040101

Get the idea?

blindman|||I get an arithmetic error all the way up to today.|||What if you hardcode a sample value from your recordset?:

select convert(datetime,cast(20031011 as varchar(8)))
from table_name

blindman|||It works.|||How about doing a

bcp yourdatabase.ownername.datestimes out c:\datestimes.txt -c -T -a 65535

on your server at command prompt and posting the file over here.|||I can't. Its stuff that can't leave here.|||No problems mate

USE Northwind
GO

CREATE TABLE datestimes (firmfile varchar(20),orddate int)
GO

INSERT INTO datestimes(firmfile,orddate )
SELECT '03000004', 20030724
GO

Can you cut and paste that in to QA and see if it runs?

Did s/he sday that this sql server...if it's mySQL...I ougtta...

bang...zoom..|||Right to the moon, Alice...

Ok the table is made.
I still get the same results.

select convert(char(10),column_name,101)
from table_name

For this, it returns the original string.|||Is this SQL Server ? If it is , can you tell us the version

select @.@.version|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)|||Originally posted by exdter
Right to the moon, Alice...

Ok the table is made.
I still get the same results.

select convert(char(10),column_name,101)
from table_name

For this, it returns the original string.

But that's not what I posted...

Did you cut and paste what I posted in to Query Analyser?

Did it fail?

I don't believe it...

The other thing is what does DBCC CHECHTABLE(datestimes)

Tell you?|||I cut and pasted it to pubs. There were no error messages on the DBCC CHECKTABLE(datestimes). I assume you meant checKtable as you put checHtable|||Originally posted by exdter
I cut and pasted it to pubs. There were no error messages on the DBCC CHECKTABLE(datestimes). I assume you meant checKtable as you put checHtable

damn hangover...

You cut and pasted it in to Pubs...and..it worked/didn't work?

works for me...

And you did the DBCC against the table you're having the problem with correct?|||Yes to all.|||Originally posted by Brett Kaiser
You cut and pasted it in to Pubs...and..it worked/didn't work?


[beating dead horse repeatedly]
But it's not a yes or no question...
[/beating dead horse repeatedly]

[:-)]|||I cut and pasted into pubs and the table was made. When I try to do the conversions, I get the same replies as on the tables I am trying to do the conversion.|||Ok, wait...and this code...sorry

select convert(datetime,cast(orddate as varchar(8)))
from datestimes
where orddate between 19950101 and 20040101

Does that run in Pubs?|||Works like a charm. The date appears as I want it to.|||I put that on my original table and it WORKED!!! THANKS!!!!!

I see. I used this:
select convert(cast(orddate as varchar(8)))
from datestimes

You gave me this:
select convert(datetime,cast(orddate as varchar(8)))
from datestimes
I didn't have DATETIME,cast in mine.|||[smacking head with hand]
That's what blindman gave a couple of hours ago
[/smacking head with hand]

I was trying to give a whole snippet of code to run, and left off the select

Look up BETWEEN in BOL, but it basically does what it says, inclusively.

NEXT!|||...because you have bad date values either less than 19950101 or greater than 20040101. You need to find them. Try the zeroing in method again.

blindman|||Now I'm totally lost. I put the queries in that you gave me to try and zero in and they all work now. I do it without the 'between'. I'm not making this up. It doesn't matter. At least its worked out. Thanks to all.|||Or you can say in the WHERE Clause

select convert(datetime,cast(orddate as varchar(8)))
from datestimes
where orddate between 19950101 and 20040101
and ISDATE(OrdDate) = 1

NEXT?|||It works without the between now. I have no idea why. Anyway, thanks!!!|||Brett... Still need another one ?|||Voodoo and dead chickens.

DATEPART with 2 digits

I am trying to get the day or month part of the date that are less than 10
with 2 digits but I can not get it.
For example I want to get 07 and not only 7. Is there a way I can get this.
Following is what I am using but it does not give me what I want:
SELECT LEFT(DATEPART(m, GETDATE()),2)
Thanks for any help
Try,
SELECT right('0' + ltrim(DATEPART(m, GETDATE())), 2)
AMB
"DXC" wrote:

> I am trying to get the day or month part of the date that are less than 10
> with 2 digits but I can not get it.
> For example I want to get 07 and not only 7. Is there a way I can get this.
> Following is what I am using but it does not give me what I want:
> SELECT LEFT(DATEPART(m, GETDATE()),2)
> Thanks for any help
>
|||As DATEPART returns an integer value, you cannot start using string
functions upon it. If you want a string, try this:
SELECT LEFT('0'+CONVERT(varchar(2), DATEPART(m, GETDATE())), 2)
R
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:CAF52094-754B-4E57-87BD-501247EBA683@.microsoft.com...
> I am trying to get the day or month part of the date that are less than 10
> with 2 digits but I can not get it.
> For example I want to get 07 and not only 7. Is there a way I can get
this.
> Following is what I am using but it does not give me what I want:
> SELECT LEFT(DATEPART(m, GETDATE()),2)
> Thanks for any help
>
|||Thanks......That did it but I hope I get the correct dates for the dates 10
and grater.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Try,
> SELECT right('0' + ltrim(DATEPART(m, GETDATE())), 2)
>
> AMB
>
> "DXC" wrote:
|||Ooops, silly me. Of course it should have been RIGHT, not LEFT, as Alejandro
has posted.
"R" <anon@.spamme.please> wrote in message
news:e9RAZOKVFHA.544@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> As DATEPART returns an integer value, you cannot start using string
> functions upon it. If you want a string, try this:
> SELECT LEFT('0'+CONVERT(varchar(2), DATEPART(m, GETDATE())), 2)
> R
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:CAF52094-754B-4E57-87BD-501247EBA683@.microsoft.com...
10
> this.
>
|||That is fine.....I tested and it works with the dates beyond 10. I just did
not want to end up with the dates like 012 or 027.
Thanks.
"R" wrote:

> Ooops, silly me. Of course it should have been RIGHT, not LEFT, as Alejandro
> has posted.
> "R" <anon@.spamme.please> wrote in message
> news:e9RAZOKVFHA.544@.TK2MSFTNGP15.phx.gbl...
> 10
>
>

DATEPART with 2 digits

I am trying to get the day or month part of the date that are less than 10
with 2 digits but I can not get it.
For example I want to get 07 and not only 7. Is there a way I can get this.
Following is what I am using but it does not give me what I want:
SELECT LEFT(DATEPART(m, GETDATE()),2)
Thanks for any helpTry,
SELECT right('0' + ltrim(DATEPART(m, GETDATE())), 2)
AMB
"DXC" wrote:
> I am trying to get the day or month part of the date that are less than 10
> with 2 digits but I can not get it.
> For example I want to get 07 and not only 7. Is there a way I can get this.
> Following is what I am using but it does not give me what I want:
> SELECT LEFT(DATEPART(m, GETDATE()),2)
> Thanks for any help
>|||As DATEPART returns an integer value, you cannot start using string
functions upon it. If you want a string, try this:
SELECT LEFT('0'+CONVERT(varchar(2), DATEPART(m, GETDATE())), 2)
R
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:CAF52094-754B-4E57-87BD-501247EBA683@.microsoft.com...
> I am trying to get the day or month part of the date that are less than 10
> with 2 digits but I can not get it.
> For example I want to get 07 and not only 7. Is there a way I can get
this.
> Following is what I am using but it does not give me what I want:
> SELECT LEFT(DATEPART(m, GETDATE()),2)
> Thanks for any help
>|||Thanks......That did it but I hope I get the correct dates for the dates 10
and grater.
"Alejandro Mesa" wrote:
> Try,
> SELECT right('0' + ltrim(DATEPART(m, GETDATE())), 2)
>
> AMB
>
> "DXC" wrote:
> > I am trying to get the day or month part of the date that are less than 10
> > with 2 digits but I can not get it.
> >
> > For example I want to get 07 and not only 7. Is there a way I can get this.
> > Following is what I am using but it does not give me what I want:
> >
> > SELECT LEFT(DATEPART(m, GETDATE()),2)
> >
> > Thanks for any help
> >|||Ooops, silly me. Of course it should have been RIGHT, not LEFT, as Alejandro
has posted.
"R" <anon@.spamme.please> wrote in message
news:e9RAZOKVFHA.544@.TK2MSFTNGP15.phx.gbl...
> As DATEPART returns an integer value, you cannot start using string
> functions upon it. If you want a string, try this:
> SELECT LEFT('0'+CONVERT(varchar(2), DATEPART(m, GETDATE())), 2)
> R
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:CAF52094-754B-4E57-87BD-501247EBA683@.microsoft.com...
> > I am trying to get the day or month part of the date that are less than
10
> > with 2 digits but I can not get it.
> >
> > For example I want to get 07 and not only 7. Is there a way I can get
> this.
> > Following is what I am using but it does not give me what I want:
> >
> > SELECT LEFT(DATEPART(m, GETDATE()),2)
> >
> > Thanks for any help
> >
>|||That is fine.....I tested and it works with the dates beyond 10. I just did
not want to end up with the dates like 012 or 027.
Thanks.
"R" wrote:
> Ooops, silly me. Of course it should have been RIGHT, not LEFT, as Alejandro
> has posted.
> "R" <anon@.spamme.please> wrote in message
> news:e9RAZOKVFHA.544@.TK2MSFTNGP15.phx.gbl...
> > As DATEPART returns an integer value, you cannot start using string
> > functions upon it. If you want a string, try this:
> >
> > SELECT LEFT('0'+CONVERT(varchar(2), DATEPART(m, GETDATE())), 2)
> >
> > R
> >
> > "DXC" <DXC@.discussions.microsoft.com> wrote in message
> > news:CAF52094-754B-4E57-87BD-501247EBA683@.microsoft.com...
> > > I am trying to get the day or month part of the date that are less than
> 10
> > > with 2 digits but I can not get it.
> > >
> > > For example I want to get 07 and not only 7. Is there a way I can get
> > this.
> > > Following is what I am using but it does not give me what I want:
> > >
> > > SELECT LEFT(DATEPART(m, GETDATE()),2)
> > >
> > > Thanks for any help
> > >
> >
> >
>
>

DATEPART with 2 digits

I am trying to get the day or month part of the date that are less than 10
with 2 digits but I can not get it.
For example I want to get 07 and not only 7. Is there a way I can get this.
Following is what I am using but it does not give me what I want:
SELECT LEFT(DATEPART(m, GETDATE()),2)
Thanks for any helpTry,
SELECT right('0' + ltrim(DATEPART(m, GETDATE())), 2)
AMB
"DXC" wrote:

> I am trying to get the day or month part of the date that are less than 10
> with 2 digits but I can not get it.
> For example I want to get 07 and not only 7. Is there a way I can get this
.
> Following is what I am using but it does not give me what I want:
> SELECT LEFT(DATEPART(m, GETDATE()),2)
> Thanks for any help
>|||As DATEPART returns an integer value, you cannot start using string
functions upon it. If you want a string, try this:
SELECT LEFT('0'+CONVERT(varchar(2), DATEPART(m, GETDATE())), 2)
R
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:CAF52094-754B-4E57-87BD-501247EBA683@.microsoft.com...
> I am trying to get the day or month part of the date that are less than 10
> with 2 digits but I can not get it.
> For example I want to get 07 and not only 7. Is there a way I can get
this.
> Following is what I am using but it does not give me what I want:
> SELECT LEFT(DATEPART(m, GETDATE()),2)
> Thanks for any help
>|||Thanks......That did it but I hope I get the correct dates for the dates 1
0
and grater.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Try,
> SELECT right('0' + ltrim(DATEPART(m, GETDATE())), 2)
>
> AMB
>
> "DXC" wrote:
>|||Ooops, silly me. Of course it should have been RIGHT, not LEFT, as Alejandro
has posted.
"R" <anon@.spamme.please> wrote in message
news:e9RAZOKVFHA.544@.TK2MSFTNGP15.phx.gbl...
> As DATEPART returns an integer value, you cannot start using string
> functions upon it. If you want a string, try this:
> SELECT LEFT('0'+CONVERT(varchar(2), DATEPART(m, GETDATE())), 2)
> R
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:CAF52094-754B-4E57-87BD-501247EBA683@.microsoft.com...
10[vbcol=seagreen]
> this.
>|||That is fine.....I tested and it works with the dates beyond 10. I just did
not want to end up with the dates like 012 or 027.
Thanks.
"R" wrote:

> Ooops, silly me. Of course it should have been RIGHT, not LEFT, as Alejand
ro
> has posted.
> "R" <anon@.spamme.please> wrote in message
> news:e9RAZOKVFHA.544@.TK2MSFTNGP15.phx.gbl...
> 10
>
>

Datepart query example help needed or something else?

The field itemdate is a datetime field in sqlserver2000 DB

This works fine:
Select Id From BookData Where (MONTH(itemdate) = '01') and
(DAY(itemdate) = '02') and (YEAR(itemdate) = '2005') order by
TitleDuplicate

This does not work:
Select Id From BookData Where (MONTH(itemdate) = '01') and
(DAY(itemdate) = '02') and (YEAR(itemdate) = '2005') and (datepart(Hh,
itemdate) = '06') and (datepart(Mi, itemdate) = '17') and (datepart(Ss,
itemdate) = '15') order by TitleDuplicate

It returns zero records even though the datetime field has a record
with the datetime value in it of:
1/2/2005 6:17:15 PM

I tried a bunch of different things like datepart(hh,itemdate) ='6'"
etc...

the DB script for this datetime field sets it as:
"ItemDate DATETIME DEFAULT '',"

Any ideas on what is going on or what am I missing ?
Thanks for any help you can give.Where convert(varchar(8),itemdate,112) = '20050102'
and convert(varchar(8),itemdate,108) = '06:17:15'

Note - the datepart valuse you have are int's not chars.

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||It is solved!
My own fault for not thinking of a 24 hour clock.
The hours I should have tried was 18 not 6 . I was thinking 6 AM when
it was 6
PM. It really had me bugged out
The code was working all along.

date/time or date and time

I am setting up an SQL database and I will need to get differences in dates. For example I have a start date, start time, completion date and completion time and I want to get the difference between the start and completion.
Would it be better to have one field with both date and time in it, or better to have a date field and a time field?
Even though I have already started setting up the tables with seperate fields for date and time I am now leaning toward one field with date/time in it. (Only because that is the way I had to do it when setting up an Excel spreadsheet for a similar task)

When that is decided could someone please point me to a good resource for explaining to me the iConvertible method. I tried a simple asp.net page to insert a record into the database and got an error method telling me I had to use the iConvertible method. (I am programming in C#). I use a textbox on a webform to input the date and time information and the SQL database fields are set up as date/time. I looked at the visual studio documentation but that doesn't help me much. It doesn't show me the syntax required and how to "use" the method.
Thanksone field with datetime should be good...easier for maintenance too..and it will solve your puspose too...

** no idea abt iConvertible...sorry

hth|||You can use DataType as DateTime and to get the difference between the Dates you can use theDateTime.Subtract()