MS SQL

Date Problem

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Sep 2006

Posts: 20

Reputation: saurabh_kanwar is an unknown quantity at this point

Solved Threads: 0

saurabh_kanwar saurabh_kanwar is offline

Offline

Newbie Poster

Date Problem

Dec 5th, 2006

Hi Guys
I've got a bit of problem with date.
When I transfer data of a table to txt file the date column that appears in the table like "28/03/2001" changes to "28/3/2001 0:00:00" in the txt file. I've tried to change the format of date in the table but it doesn't help.I'm using the following code.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
Private Sub Command2_Click()
DoCmd.TransferText acExportDelim, "PESALES Export Specification", "PESALES", "J:\Power Equipment\Common\SK\PESALES.TXT"
End Sub
Private Sub Command2_Click()
DoCmd.TransferText acExportDelim, "PESALES Export Specification", "PESALES", "J:\Power Equipment\Common\SK\PESALES.TXT"
End Sub

Thanks for your time

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: Date Problem

Dec 20th, 2006

this seems more like a code problem than an sql problem. try posting in the appropriate forum for the language you are using. (vbscript?)

•

Join Date: Jan 2007

Posts: 3

Reputation: KeiHimekawa is an unknown quantity at this point

Solved Threads: 0

KeiHimekawa KeiHimekawa is offline

Offline

Newbie Poster

Re: Date Problem

Jan 9th, 2007

I've been having such problem before. The reason why it chages "28/03/2001" to "28/3/2001 0:00:00" is because SQL requires most date/time parameter to be changed to such format. What I do is use CAST and SUBSTRING to convert the date intolong date.

Example: From "28/3/2001 0:00:00" I chaged it into "March 28, 2001"

Here's the code:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT [whatever's the name of your date field], 
     CASE WHEN [date field] IS NOT NULL THEN 
           (SELECT SUBSTRING(CAST([date field] AS VARCHAR), 1, 11)) 
END AS [new date field name]  
FROM [whatever table you're USING]
SELECT [whatever's the name of your date field], 
     CASE WHEN [date field] IS NOT NULL THEN 
           (SELECT SUBSTRING(CAST([date field] AS VARCHAR), 1, 11)) 
END AS [new date field name]  
FROM [whatever table you're using]