[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

Change date format in the database MS SQL

Hello Everyone,
My dates appear in the following format in my database: 5/22/2006
I would like to change them to this format: 2006-5-22

The names of the columns are "startDate" and "endDate" and the name of the table is Table1

Can you help me change the format without affecting the data?  Is it possible?


0
Gemini532
Asked:
Gemini532
  • 3
  • 3
  • 2
  • +2
4 Solutions
 
LowfatspreadCommented:
this is a display issue...

your dates are only stored in an internal date format...

all you can effect is the visual representation...

either use

convert(varchar(26),yourdate,112)

of set dateformat YMD

hth
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Gemini532,
> Can you help me change the format without affecting the data?  Is it possible?
the data wont be affected if you change the datefoemat

in order to change the settings, you beed to change the default language of the user

run sp_helpLanguage  in QA and change the language ccordingly.
But i suggest Leave it as it is and use ' SET DATEFORMAT YMD' While retrieving the data
0
 
Scott PletcherSenior DBACommented:
Keep in mind that SET DATEFORMAT applies *only* to *input*, not to output / display.  It will make no difference to how a date is displayed, which is controlled by the default language.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
imran_fastCommented:
hi  Gemini532,
I think you are mentioning the date format in enterprise Manager. That the internal date format as  Lowfatspread
suggested. you can change the display date format in query analyzer by using convert function.

and from your application you have to change it in the regional object. to diplay it in the desired format.
0
 
Gemini532Author Commented:
So the dates are in this format   10/31/1976 in the database because they were inserted that way into MS ACCESS.
I moved the data from ACCESS TO MS SQL and the dates are still in the same format.

The problem is that when I try to insert new dates from the same code that worked while it was inserting into ACCESS, I am getting this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

/AH_environasp/WriteToDBEmail.asp, line 130

If I do a response.write of the SQL statment I get:

INSERT INTO Table1 (startDate, endDate, Location, Description, postingDate, requestedBy, ccedTo, Other, EIA, EIAComments, EIALink) VALUES ('5-25-2006','7-24-2006','loc','pro','No','2006-5-25','','','','*','')

This is becasue when the dates where in the format 5/25/2006, I was getting the same error, so I tried to see if the error would go away if I change the format, but it didn't...

This is my code:

if len(aspStartDate)=0 then
      'aspStartDate = date()
      aspStartDate = month(date()) & "-" & day(date())  & "-" & Year(date())
      response.write("Start Date" & aspStartDate & "<br>")
      
else
      'aspStartDate = request("txtStartMonth") & "/" & request("txtStartDay") & "/" & request("txtStartYear")
       aspStartDate = request("txtStartMonth") & "-" & Request.Form("txtStartDay") & "-" & Request("txtStartYear")
       response.write("Start Date else" & aspStartDate & "<br>")
      
end if

'original code
'aspPostingDate=Request.Form("txtPostMonth") & "/" & Request("txtPostDay") & "/" & request("txtPostYear")

'new code
'aspPostingDate=cstr(Request.Form("txtPostMonth")) & "/" & cstr(Request.form("txtPostDay")) & "/" & cstr(request.form("txtPostYear"))

aspPostingDate = Request.Form("txtPostMonth") & "-" & Request("txtPostDay") & "-" & request("txtPostYear")

I have tried everything I could think of and nothing seems to get rid of this error!  I am completly lost...
I know it has something to do with the conversion from ACCESS TO MS SQL.  That somehow the code that worked in access, doesn't insert into MS SQL and gives me this error!

CAN YOU HELP ME !?!  
0
 
LowfatspreadCommented:
the safest Date style to always use internally is

YYYYMMDD HH:MM:SS.sss  

as a string

so in your case i'd suggest always formating the Date as YYYYMMDD  e.g. 20060525 for 25th May 2006...


hth
0
 
Gemini532Author Commented:
When you say as a string do you mean the datatype in MS SQL should be varchar ?
Right now it's datetime.  I'm afraid I cannot change it in the database.  I do not have the authority.  Plus it is too dangerous to modify the database in this way...

I really need help changing the code to fit the database.
I did a response.write on this SQL statment and this is what i get:

INSERT INTO Table1 (startDate, endDate, Location, Description, postingDate, requestedBy, ccedTo, Other, EIA, EIAComments, EIALink) VALUES ('5-30-2006','7-29-2006','LOC','PROJECT','No','5-30-2006','ahont@opic.gov','','','*','')


Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

/ah_environasp/WriteToDBEmail.asp, line 131

Angie
0
 
Gemini532Author Commented:
Maybe somehow I have to convert the string into a datatime variable, how can I do that?  Any ideas?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Its not the errror with date variables, In  your Queries you are trying to insert the value   'NO'      to  the field

PostingDate ...

INSERT INTO Table1 (startDate, endDate, Location, Description, postingDate, requestedBy, ccedTo, Other, EIA, EIAComments, EIALink)
VALUES ('5-30-2006','7-29-2006','LOC','PROJECT','5-30-2006','ahont@opic.gov','','','*','')

0
 
LowfatspreadCommented:
no in the database keep the column as a datatime ...
just when you insert or update or select against it

supply the value  as a String in  'YYYYMMDD HH:MM:SS.sss' format
and SQL Server will never be confused about which set of values is which...

its an application formating issue...
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now