Datetime datatype conversion to int hhmmyy format

Is there a way of converting a datetime data type in the form [DD/MM/YYYY HH:MM:SS] to an integer containing just the time in the form [HHMMSS].SELECT REPLACE((CONVERT(VARCHAR,GETDATE(),24)),':','')

This converts it to a varchar. You actually don't want to convert it to integer as it will drop off the leading zeros.

dateformat is ignored

Hello,

I receive a file containing some character fields along with a date.
The date values in the file are formatted as "dd/mm/yy", that is
2-digit day, 2-digit month, and 2-digit year. The separator could be
slash or a dash ("-"). The file is in a proprietary format, and bcp is
not an option.

So, I decided to load the file using a prepared statement. I open a
cursor with an INSERT statement, read from the file, parse out values,
and put it in the database using the cursor. All is OK; except that
the date values are mangled. This is despite the fact that I am issuing
a "set dateformat dmy" before running the INSERT statement.

It seems that the "set dateformat dmy" is not being accepted, or it is
being ignored. I set it at the beginning right after opening a
connection to the database. From what I understand, it should work.
Am I doing something wrong? Any suggestions on how to get this to
work?

Thanks!newtophp2000@.yahoo.com wrote:

> Hello,
> I receive a file containing some character fields along with a date.
> The date values in the file are formatted as "dd/mm/yy", that is
> 2-digit day, 2-digit month, and 2-digit year. The separator could be
> slash or a dash ("-"). The file is in a proprietary format, and bcp is
> not an option.
> So, I decided to load the file using a prepared statement. I open a
> cursor with an INSERT statement, read from the file, parse out values,
> and put it in the database using the cursor. All is OK; except that
> the date values are mangled. This is despite the fact that I am issuing
> a "set dateformat dmy" before running the INSERT statement.
> It seems that the "set dateformat dmy" is not being accepted, or it is
> being ignored. I set it at the beginning right after opening a
> connection to the database. From what I understand, it should work.
> Am I doing something wrong? Any suggestions on how to get this to
> work?
> Thanks!

You say BCP isn't an option but you didn't explain what other method
you are using to read the file or why a cursor is necessary. Don't rely
on SET DATEFORMAT. Use the CONVERT function with the style parameter to
specify the exact format. Looks like style 3 or 103 is what you need.

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> You say BCP isn't an option but you didn't explain what other method
> you are using to read the file or why a cursor is necessary. Don't rely
> on SET DATEFORMAT. Use the CONVERT function with the style parameter to
> specify the exact format. Looks like style 3 or 103 is what you need.

I read from the file line by line and parse the line to extract the
fields. I then use the bound variables in the prepared Insert
statement to add it to the database. I wanted to change the DATEFORMAT
configuration as it seemed to be such a straight answer. I guess I
could use the CONVERT function if it is fast enough. I can do some
tests to see how it performs.

I am curius: is there a particular reason to shy away from setting
DATEFORMAT? Is it not reliable as implemented or something else?

Thanks a lot!

> --
> David Portas
> SQL Server MVP
> --|||Hi

If you are parsing a string then you constructing the date in CCYYMMDD
format will be a safe option.

John

<newtophp2000@.yahoo.com> wrote in message
news:1135777730.480129.321010@.z14g2000cwz.googlegr oups.com...
> David Portas wrote:
>> You say BCP isn't an option but you didn't explain what other method
>> you are using to read the file or why a cursor is necessary. Don't rely
>> on SET DATEFORMAT. Use the CONVERT function with the style parameter to
>> specify the exact format. Looks like style 3 or 103 is what you need.
>
> I read from the file line by line and parse the line to extract the
> fields. I then use the bound variables in the prepared Insert
> statement to add it to the database. I wanted to change the DATEFORMAT
> configuration as it seemed to be such a straight answer. I guess I
> could use the CONVERT function if it is fast enough. I can do some
> tests to see how it performs.
> I am curius: is there a particular reason to shy away from setting
> DATEFORMAT? Is it not reliable as implemented or something else?
> Thanks a lot!
>
>> --
>> David Portas
>> SQL Server MVP
>> --|||David and John,

Thank you very much for your input. I am now using the techniques that
you suggested and it works great!

DATEADD returning odd count of records

Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qhDATEADD(m, -18, getdate()) is giving you 26th Jan 2004. That means
everything older than or equal to 26th Jan 2004. But your second query is
looking for data between 1st and 31st of Jan, which obviously is going to
have 5 days worth of additional data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1122371409.746888.44830@.g49g2000cwa.googlegroups.com...
Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qh|||Hello, Scott
You should notice the fact that "DATEADD(m, -18, getdate())"
returns January 26, 2004 and in your second query you are
comparing with January 31, 2004.
If you want to obtain the last day of the current month,
you can use the following:
SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
So your query might be:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
Razvan|||Hi Razvan,
Cheers for the reply, your solution sorted it. I didn't expect the
count to be over 200k for the days 26-31st Jan. I think it's myself
who doesn't know his own data!
;o)
Thanks
Scott
Razvan Socol wrote:
> Hello, Scott
> You should notice the fact that "DATEADD(m, -18, getdate())"
> returns January 26, 2004 and in your second query you are
> comparing with January 31, 2004.
> If you want to obtain the last day of the current month,
> you can use the following:
> SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
> So your query might be:
> select count(*) from MyTable
> where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
> Razvan