Date formatting problems

Hi,

I have around 1000 records each with two dates in a database in MSDE onmy PC. I need to move the data to an on line SQL server and havetried to use Microsoft Web Data Administrator to do this. I canexport the data from the MSDE to an SQL file but it will not importbecause the date format in the SQL file is "dd,mm,yyyy".

If I export from either the MSDE or the SQL server both produce fileswith date in the format "dd,mm,yy" and yet I cannot import either!?!

It is impractical to change all the dates by hand. I am on abudget and do not have access to anything other than free software.

Can anyone advise me of the best way forward.

Thanks in anticipation.

MikeIf you have compatible versions of MSDE and SQL Server, you can detatch the msde database and reattach it to sql server without going through the export/import|||Hi

I am not sure if they are "compatable". The sql server is on myweb servers computer and I know little about it. I don't know howto attach or reattach tables but I will check on sql server 2000 bookson line.

Thanks

Mike|||

Mike,

Maybe you could import the data to a varchar column (instead of adatetime column), and write a small query to fix the formatting.

|||Hi

I have checked Books On Line and attaching a database does not sound tobe an option. I do not have access to the files of the SQLserver, but can access it through Web Data Administrator orADO.NET. Also, I want to add data to a single table withoutdisturbing the rest of my database.
Any one got any other ideas?

Mike|||Hi Geojan

That sounds an interesting idea. Not sure exactly how to do itbut I would have thought it should work OK. I could even writesome VB.NET code to do it for me (I am probably better with VB thanqueries). I am not usually quick at this sort of thing but willlet you know in the next day or two how I get on.

Thanks

Mike|||It pretty simple actually, say you have a Table (TblA), that's where you import your data, and the data column is defined as varchar(10) (named vc_Date).

And you have secound table (TblB), where the data column is defined as datetime (named dt_Date).

The sql statement needed to transfer the data from TblA to TblB, would be (Just for the example I added ColB, ColC and ColD):

Insert TabB (dt_Date, ColB, ColC, ColD)
Select Convert(datetime, Substring(Vc_Date, 7, 4) + Substring(Vc_Date, 4, 2) + Substring(Vc_Date, 1, 2))
,ColB, ColC, ColD
From TblA

If you like you can execute the statement form vb, or simply run it in query analyzer.

|||Hi Geojan

I ran the sql statement as an ExecuteNonQuery command and it worksgreat. My dates have been inserted correctly into the appropriatecolumns. Many thanks for the advice.

Mike|||

Thanks for the feedback Mike, nice to know it helped!

Date Format in SQL (MSDE)

Hi,
Can anyone help with the following:
I am importing a XML file into a SQL (MSDE) database table, and one
field is set as follows:
01/11/2005 00:03:33.18
01/11/2005 00:04:31.12
01/11/2005 00:10:06.32
01/11/2005 00:12:20.59
01/11/2005 00:00:56.66
I am using access to manage MSDE, and when I go to the table design and
try to format this field as "General Date" or any other pre-set date
format, it give the "Access encountered an error" message and then
deletes everything in that field.
Does anyone know how I can format the above time format into something
that will work with MSDE?
Thanks in advance for any help.
Thanks
H01/11/2005 00:03:33.18 works on SQL Server but not in Access.
Access only recognizes seconds (00:00:00 to 23:59:59) so 01/11/2005 00:03:33
will work.
If you need miliseconds perhaps you can use another tool.
Ben Nevarez
<harrys@.gmail.com> wrote in message
news:1131930009.458433.191620@.g43g2000cwa.googlegroups.com...
> Hi,
> Can anyone help with the following:
> I am importing a XML file into a SQL (MSDE) database table, and one
> field is set as follows:
> 01/11/2005 00:03:33.18
> 01/11/2005 00:04:31.12
> 01/11/2005 00:10:06.32
> 01/11/2005 00:12:20.59
> 01/11/2005 00:00:56.66
> I am using access to manage MSDE, and when I go to the table design and
> try to format this field as "General Date" or any other pre-set date
> format, it give the "Access encountered an error" message and then
> deletes everything in that field.
> Does anyone know how I can format the above time format into something
> that will work with MSDE?
> Thanks in advance for any help.
> Thanks
> H
>|||Hi Ben.
Problem is, that if I try and covert it to the 00:00:00 format, access
doesn't like that so it just deletes all the records!
And at the moment there is no other way to get the XML file, apart from
with those miliseconds in there.
H
Ben Nevarez wrote:
> 01/11/2005 00:03:33.18 works on SQL Server but not in Access.
> Access only recognizes seconds (00:00:00 to 23:59:59) so 01/11/2005 00:03:
33
> will work.
> If you need miliseconds perhaps you can use another tool.
> Ben Nevarez
>
>

Date format in MSDE SQL

Hello,
I was developping with SQL Server and now I'm testing with MSDE.
I meet a problem with SQL containing dates. My SQL is :
INSERT INTO PIPT_Campaigns(CAM_CampaignId, CAM_CampaignCode,
CAM_CampaignLabel, CAM_CampaignStartDate, CAM_CampaignEndDate,
CAM_CampaignStatus) VALUES (5, 'C0405', 'Campagne 2004-2005', '01/08/2004',
'28/02/2005', 0)
Is there anything wrong with the date format ? The corresponding columns are
defined as datetime.
Thanks for any answer.
Ouaf wrote:

> Hello,
> I was developping with SQL Server and now I'm testing with MSDE.
> I meet a problem with SQL containing dates. My SQL is :
> INSERT INTO PIPT_Campaigns(CAM_CampaignId, CAM_CampaignCode,
> CAM_CampaignLabel, CAM_CampaignStartDate, CAM_CampaignEndDate,
> CAM_CampaignStatus) VALUES (5, 'C0405', 'Campagne 2004-2005', '01/08/2004',
> '28/02/2005', 0)
> Is there anything wrong with the date format ? The corresponding columns are
> defined as datetime.
>
use yyyy-mm-dd as dateformat because it is language independend
Jens

>
|||On Thu, 29 Jul 2004 17:28:02 +0200, Ouaf wrote:

>Hello,
>I was developping with SQL Server and now I'm testing with MSDE.
>I meet a problem with SQL containing dates. My SQL is :
>INSERT INTO PIPT_Campaigns(CAM_CampaignId, CAM_CampaignCode,
>CAM_CampaignLabel, CAM_CampaignStartDate, CAM_CampaignEndDate,
>CAM_CampaignStatus) VALUES (5, 'C0405', 'Campagne 2004-2005', '01/08/2004',
>'28/02/2005', 0)
>Is there anything wrong with the date format ? The corresponding columns are
>defined as datetime.
>Thanks for any answer.
>
Hi Ouaf,
The date '28/02/2005' will probably be interpreted correctly, but
'01/08/2004' is ambiguous - it could be either DD/MM/YYYY or MM/DD/YYYY
(the default format in -amongst others- the USA).
It's best to use the internation standard date format YYYY-MM-DD; that is
always interpreted correctly.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Always use YYYYMMDD with SQL Server.
The following formats are all vulnerable to misinterpretations or errors due
to regional or language settings:
mm/dd/yyyy
dd/mm/yyyy
yyyy-mm-dd
http://www.aspfaq.com/
(Reverse address to reply.)
"Ouaf" <ouaf@.microsoft.com> wrote in message
news:#oO1kCYdEHA.2812@.tk2msftngp13.phx.gbl...
> Hello,
> I was developping with SQL Server and now I'm testing with MSDE.
> I meet a problem with SQL containing dates. My SQL is :
> INSERT INTO PIPT_Campaigns(CAM_CampaignId, CAM_CampaignCode,
> CAM_CampaignLabel, CAM_CampaignStartDate, CAM_CampaignEndDate,
> CAM_CampaignStatus) VALUES (5, 'C0405', 'Campagne 2004-2005',
'01/08/2004',
> '28/02/2005', 0)
> Is there anything wrong with the date format ? The corresponding columns
are
> defined as datetime.
> Thanks for any answer.
>