SQL 2k w2k server sp2
When I first installed the SQL server on my server, the default date
format was mm/dd/yy. Now I need to change that to dd/mm/yy in
regional settings, but its still not reflected in the DB's in SQL (its
still mm/dd/yy).
Is it possible to change it in SQL without a complete reinstallation
of the server?
Aim – Currently i have column “[LAST-STATUS-CHG]” in the following date format “042312” i need this date format to be changed into “23-04-12”.
Also i would like an additional column created called “Start of month” this column should look at “[LAST-STATUS-CHG]” and what ever the day is in the defauly it to 01
Current results
FDMSAccountNoACCOUNT-STATUSLAST-STATUS-CHG
87800000088416042312
87800000088513011212
87800000088612100712
Desired results
FDMSAccountNoACCOUNT-STATUSLAST-STATUS-CHGStart_of_month
8780000008841623-04-1201-04-12
8780000008851301-12-1201-12-12
8780000008861210-07-1201-07-12
My query is
SELECT [FDMSAccountNo]
,[ACCOUNT-STATUS],
[LAST-STATUS-CHG]
FROM [FDMS].[dbo].[stg_LMPAB501]
where FDMSAccountNo = '878000000884'
Howdy,
I live in Australia and use the date format of - dd/mm/yyy, but when i do a query in SQL Management it seems to want its inputs in US format mm/dd/yyy.
For example it errors when i do a search and one of the dates is 27/11/2007, ie there is no 27th "US" Month.
I have checked my local settings (regional and language in control panel), and it all points to Australian formats, it just seems the SMS wants the dates in US.
Any Ideas?
Dwayne Schaffarz
My current Query takes the DATE value stored in P.CreatedDate and makes it VARCHAR, then stores it in the table. I need to format this to return YYYYMMDD. How would I go about this?
Current Code:
Code:
CAST(P.CreatedDate AS VARCHAR) AS DateEntered,
Fields!TaskStartDate.Value & Fields!TaskName.Value & Fields!StatusForExecutiveReporting.Value & Fields!NotesForExecutiveReport.Value
This is my expression in a cell.when i run my report task start date value is something like the:
01/25/2015 8:00:00AM.
I want this date to be in this form: jan 2015
Hi,
I have to generate a daily report of survey answers by users? My question is there a way to reformat the query so it generates a table or report with it showing the rows as columns instead.
Here is my initial query.
SELECT
dbo.Reporting_SurveyAnswers.DateCreated AS DateCreated
,dbo.Reporting_SurveyAnswers.questionid AS QuestionID
,dbo.Reporting_SurveyAnswers.surveyid AS SurveyID
,dbo.Reporting_SurveyQuestions.ordernumber AS OrderNumber
,dbo.Reporting_SurveyAnswers.userid AS UserID
,dbo.Reporting_User.LastName1 AS LastName
,dbo.Reporting_User.FirstName AS FirstName
,dbo.Reporting_SurveyQuestions.QuestionText AS QuestionText
,dbo.Reporting_SurveyAnswers.QuestionAnswer AS QuestionAnswer
FROM
dbo.Reporting_Surveys
INNER JOIN dbo.Reporting_SurveyQuestions
ON dbo.Reporting_Surveys.surveyid = dbo.Reporting_SurveyQuestions.surveyid
INNER JOIN dbo.Reporting_SurveyAnswers
ON dbo.Reporting_SurveyQuestions.QuestionID = dbo.Reporting_SurveyAnswers.QuestionID
INNER JOIN dbo.uvwReporting_User
ON dbo.Reporting_SurveyAnswers.userid = dbo.uvReporting_User.userid
WHERE
dbo.uvReporting_SurveyAnswers.surveyid = 1125
Order by dbo.Reporting_SurveyAnswers.DateCreated
,dbo.Reporting_SurveyQuestions.ordernumber
Select
dbo.Reporting_SurveyQuestions.ordernumber AS OrderNumber
, dbo dbo.Reporting_SurveyQuestions.QuestionText AS QuestionText
To complicate matters, some of the users did not answer some of the questions and some of the questions are duplicated in the rows because the database assigned them one answer each.
Example. Question 18 says "Name all the industries you have worked in. Check all that apply.
What happens is lets say the user checks 4 different boxes. In the query results, it will show 4 rows with question 18 with each answer they checked off.
Any help would be appreciated.
Thanks
The Accidental Tourist
E.g, i have a store procedure. The start date is long date (4/15/2007 3:00pm). i want to select the start date with a particular date (short date format 4/15/2006). Thanks in advance.
View 1 Replies View Related
Hi
I am trying get my VB6 application to insert a record into a table (SQL Express) which has a datetime column but it would not process if the data format is differ to *American Date format*.
The date() function in VB returns 15/11/2006 which is in Australian Date format (DD/MM/YYYY) according to my setting in "Reginal and Lanuage Option-> Locale 0> English (Australia)" setting.
I get the following error:
Msg 242, Level 16, State 3, Server KITSQLEXPRESS, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
My computer's locale is set to English (Australia) and I expect the datetime format would follow what is set in system locale
I've read an article somewhere on the net about how SQL 2005 eliminate the confusion of date conversion when read/write datetime records into a table...but it seems to me that it is still as in-flexible as MS Access
Is there a setting in the database that takes care of it?
Thanks
Hi,
I have a column date in my database which I should send it to Oracle database. The Date format in Oracle is number. I don’t know how should I convert the date to that format?
Example :
SQL FormatOracle Format
02/16/05 105046
Thanks.
I have a SSIS data flow task that downloads data from an Oracle source which has some real dates and some date values of 0001-01-01 which I am trying to convert to '1900-01-01' using a decode(Date1,'0001-01-01','1900-01-01',Date1). The problem I am having is that when I run the package in Business Intelligence Development Studio on my local machine the date value is stored correctly in the SQL table in a datetime field as 1/1/1900 12:00:00 AM, however when I run the package from the server the field gets saved as 1/1/2001 12:00:00 AM.
I have tried a bunch of ways to work around this issue and the only solution I have found that works it to download into a temp table and then load into the live table. Does anyone have any idea of what might be causing this issue when I run the package from the server?
Hi There
We currently have the following scenario:
4 app servers with regional date and time settings of locale A.
1 database server with locale settings B.
What is happening is that timestamps are being generated on the app servers, these are then in a sql command which fails on the database server since the timestamp format is invalid.
It was suggested that we change the regional locale settings of the database server, but will this not have serious implications , for example every current timestamp format in the datbase will become invalid?
In a nutshell is it safe to change a database servers regional date time locale settings ? Or are there serious implications?
Thanx
We are migrating data from old DB2 systems to sql server 2012, the DATE FORMAT in those systems is in decimal format with 7 digits. CYYMMDD format.
I need to convert this into DD/MM/YYYY format.
Hi..I have an application which uses Sql Server 2000 and .NetMy user can view date in dd/mm/yyyy format but at the back, i send date to the Sql Server in mm/dd/yyyy fromat.But the problem is with the date format that Sql Server uses to store.It smoe times accept date in dd/mm/yyyy fromat and sometimes in mm/dd/yyyy format.I am wondering why this is happening?I have used SET DATEFORMAT....but i have to use it befor every query.Even i don't know how to set dateformat each time with vb.net program.I think i can avoide this burden for server if i can set a date format in mm/dd/yyyy fromat.What should i do?
View 6 Replies View RelatedHi Everybody, I have SQL Server running on Win NT. My question is : Is there a way to change the format Of date type fields (datetime and smalldatetime) from the US Date format which is for ex: July 13 1998 to 13 July 1998. I know I can use several functions to do it elsewhere, but I want it to become my default format for the new columns I create in new tables.
Thank you
Hi all,
I'm getting alot of date converstion trouble with my application when the end user's regional settings is not the same as the sql server box... I am using normal sqlcommands to enter my data into the sql db (vb.net), and alot of times I get users who's application breaks, because of this.
My question is... Is there some T-SQL command in sql server which will tell me the date formats it expects?
What I want to do then is query the server for these settings on startup of my application, and then set my application to use that date format for all it's date functions... This way, I'll eliminate the errors my application has to deal with.
Any ideas?
Thanks
Hello Sir,
I am using sql server 2000.whenever i fetch a record from as database.i have a date field in the table.it reurns the date with time.but i want only the time with a new format(Like- dd/MM/yyyy).So how can i do it. Pls help.
alok..........
Hi.
I'm localized in Greece and the date format used is dd/mm/yyyy.
So I have is that I made a page with a callendar. The user picks a date and this date is stored to an SQL server. But here comes the problem. When I try to write to the server the above format ( dd/mm/yyyy ) is not accepted because SQL wants date in format ( mm/dd/yyyy). So if day is bigger than 12 I get error or if is less than 13 wrong date is stored in the SQL.
Any ideas?
Thanks in advance
Hi Dear All DBAs
Does anyone know whether you can specify date format(EG dd/mm/yy or mm/dd/yy) for SQL server when you install it?
It seems to me that it always assume US date format when converting a date string to a datatime variable.
You can easily try this by running query
Select convert(smalldatetime, "30/5/2000"). It would say "...out of range..." until you use "5/30/2000". It does not seem to relate to NT regional setting(I already set it to our local setting dd/mm/yyyy).
You can use "set dateformat" in the application to get around it.
But I would like to change the default format to dd/mm/yyyy.
Any solution? (EG reinstall SQL server?)
Dear All;
Can anyone tell me how can i change the datetime format in Ms SQL Server 2000.
During i insert a data from VB to Ms SQL 2000 database, my date field return me the format as "2004-06-30 14:20:31.000". My actual format need like this "Jun 30 2004 02:20:31 PM"
I have format my coding in VB as "Format(X,"dd/MMM/YYYY HH:MM:SS"). But this look like something wrong once the data is insert to the table.
Pls help
Dear All,
I'm having a query problem regarding to the date format. From a table, there's a record of patients' birthdate.
In order to identify their age, how should i perform the query?
Select * from patient where BirthDate.Year < 1950
I've tried the above query where i want to extract patients' records who born before 1950, however, it generates error. Can somebody help?
M i K e
Hi,
I'm working with a table with more than 2 million rows. The problem is that the table has a field called "ShipDate" of type nvarchar. When I try to change the data type to DateTime sql server throws a data conversion error.
I noticed that the date format is as follow: 2006-10-23, so, is there a way sql server takes this field and change it using the field values and applying the correct format? meaning: 10/23/2006
thanks a lot
Hi,
We have a server (SQL 2005) set up as US date format
and have tried to change it to UK using syslanguage etc
Anyone know how to do it at a global level so we dont have to change lots of SP
Regards
Stu
I have date coming to one page as a string in the following format"May 4 2005 12:00AM"
I need to query one of my tables using this date in combination of other nondate values. How can I convert this date into valid sql server datetime format before I query a database tables
Please help
I need to open and read a text file using C#. Once I have read all of the values from the text file, I will need to insert some of those values into a SQL Server database table. One of the values read from the text file will be a date in the format YearMonthDay. For example: 20070601
So my problem is that I don't know how to convert this date into the date format that is expected for a SQL server datetime value.
Can someone help me out. How do I do this?
Hi,
How can I set the date format to be 'dd/MM/yyyy' for an SQL Server database (to apply in all tables' dates) ?
Thanks a lot in advance!
Does anyone know if its possible to configure sql server 2000 so it reads in the date in a different format. Right now, it takes the date in the following format month day year. I want it to take in day month year instead.
I am using asp.net and that is properly configured to give day month year, but then this date is sent to sql server, i get into problems
please help
I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.
, CASE
WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y'
ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10))
END AS 'System Verified'
How can we identify the Date Format from a String in SQL Server.
I might get an input from external source as "MM-DD-YYYY" or "DD-MM-YYYY" or "YYYY-MM-DD" or "YYYY-DD-MM", all i have to do is return the same with Current Date in the same format as Input.
Ex :
1. "02-20-2013" -> "05-28-2015"
2. "2014-04-19" -> "2015-05-28"
Any method to identify the DateFormat then it would have made the job very easy.
I need to handle date format "41981".
View 9 Replies View RelatedI have a date field stored in a character type field ( this field is a spare one in an ERP package )I am trying to validate and convert to a date format using the following;
[code="CASE WHEN isdate( arc.UserField1 )= 1 then
CAST( arc.UserField1 as DateTime )
ELSE
CAST( '01/01/1900' as Datetime )
END"] [/code]
The results are ;
Date Result
29/09/08 1900-01-01 00:00:00.000
09/06/15 2015-09-06 00:00:00.000
18/03/13 1900-01-01 00:00:00.000
09/10/14 2014-09-10 00:00:00.000
as you can see - the date 29/09/08 is a valid UK date format - however it fails the validation. 09/06/15 converts to 6th September 2015 ( mm/dd/yy ) format...What do I need to do to force this to identify as dd/mm/yy date format ?
I am facing an issue
1:- From Source system ,I am getting dates like ---
2014-Q3
2014-Q2
2014-09
As per my requirement, I need to convert this time to dates like
1:- 2014-Q3 --- last day of quarter 3 of 2014 .
2: 2014 -02 -- last day of feb 2014
Converting such format to the dates I expect...
Hi there,
Its probably easier to draw this problem than describe it, so here goes:
I have a sales forecast table (A,B,C are products [PRODUCT], the dates refer to the month for which the forecast is [FORECAST_DATE], and the integer is the forecast sales qty [FORECAST_QTY])
A 01/03/2004 30
B 01/03/2004 28
C 01/03/2004 24
A 01/04/2004 11
B 01/04/2004 09
C 01/04/2004 41
I need to convert the table into a more sensible format, like this:
(NB ...Dots are just there to help with formatting - basically I'm talking about a field for FORECAST_03_2004, FORECAST_04_2004 etc etc)
........ 01/03/2004....01/04/2004
A..........30..................11
B..........28..................09
C..........24..................41
I'm really no t-SQL guru, and I can't seem to get any joy out of BOL. It must only be a tiny bit of code. Can anyone help?
Thanks very much
Sam
Hi,
Iam migration data from a table into a comma delimited flatfile,but i need to specify all the columns within [ " ] in the flatfile
for example
i have a column [Name] the values are John,Mani,Raghu.....
The flat file should be outputted as
"John"
"Mani"
"Raghu"
Is there anyway to do this.Pls help.
Thanks,
SVGP