Hello, I'm new to SQL Server, working for a non-profit computerizing alot of its data.I imported a table of people's names, birth dates, etc. into SS2005from Access, and the birth_date was imported as an Access date/timefield, giving it the datetime datatype in SQL.The column values look like:10/14/1964 12:00:00 AMWhere and how do I learn to specify that all fields like this should bein ISO format of yyyy-mm-dd??Do I have to create a new column and put all the dates into it??Should I just convert the data in queries/views??Use a constraint to format the data??I can redo the Access table if necessary, it is only 300-some rows.I tried BOL but it was not helpful...The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will haveto be stored properly in the database table as column/fieldbirth_date...Thank you, Tom
I have table with column Date Of Birth its datatype is smalldatetime. Now I was looking for SQL Statement like I will give from date and to date as parameter it should select date of birth occurring between that date and month.
Hello Can someone help please? I'm using SQL Server 7 and trying to update a table containing over 250,000 records. I have a character(8) (CHARDOB) field representing dates of birth in the form YYYYMMDD and would like to update my datetime field (DOB) using a simple update command like:-
UPDATE MyTable SET DOB = RIGHT(CHARDOB, 2) + '/' + SUBSTRING(CHARDOB, 5, 2) + '/' + LEFT(CHARDOB, 4)
but get the message:-
Server: Msg 242, Level 16, State 3, 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.
The strange thing is that this works on a small dataset but crashes and burns on larger ones. I have tried different CAST and CONVERT options to transform the data into the correct format. I have tried a stored procedure, DTS transformations and execute SQL task in my DTS package without success.
Here's the problem. I want to insert age in years to a table of children, using a MS SQL trigger to calculate age from Date of Birth (DOB). DOB format is mm/dd/yyyy. The DOB input comes from an ASP Insert statement. I've tried to use DateDiff and a user-defined function to calculate age without any success. Also the trigger needs to account for children of less than 1 year old, could be a 0. Age is integer in the children table. Any help is greatly appreciated. The sooner the better. Even a kick start is better than nothing.
And I would like to pull them from the table using sql 2008 and have the formatting look like this:
How can I make this happen? Where numbers are missing, I would prefer to leave the area blank, ie 5551212 becomes 555-1212, and 1115551212 becomes (111)555-1212, extension numbers only where they are currently included.
Hi I'm trying to convert and format integer values in a SQL Server select statement to a string representation of the number formated with ,'s (1000000 becomes 1,000,000 for example). I've been looking at CAST and CONVERT and think the answers there somewhere. I just don'tseem to be able to work it out. Anyone out there able to help me please? Thanks,Keith.
I have a column of phone numbers that have formatting such as (xxx)xxx-xxxx, or xxx.xxx.xxxx or xxx.xxx-xxxx; however, not all phone numbers are formatted in this fashion. I need to remove all characters and only leave behind numeric digits to look like xxxxxxxxxx. I have tried select replace(colname,'-','') from table. This does the trick but does not actually change the values in the column. Any help in accomplishing this would be helpful. Thanks in advance.
SQL query: I am importing data from one source to another. The phone number has 3 variations and I want to standardize the information. I need to remove the "+1" or "1" from the leading characters of the phone number.
Once again, SSIS is giving me a 'F.U.N.' time (ask for definition of the F.U.N. acronym another time ).
I have a relatively simple task - create an excel spreadsheet with 3 columns of data - Id, Description and Sales. ID and Description are text, sales is int.
So my SP aggregates and creates my resultset in my OLE DB Source in the Data Flow. It proceeds to the Excel destination, and that all seems fine. My issue is that the data is being written as text. Looking at the excel destination in Advanced editor: the Excel Destination Input, Input columns are formatted as I expected: DT_WSTR 8 for the ID, DT_WSTR 100 for the Description and DT_I4 for the Sales. Excel Destination Input, External columns refuse to fall in line, though. They are all listed as DT_WSTR 255.
The target excel spreadsheet is being created from a template file. That template file has header columns. The target column for the Sales has the entire column formatted to NUMBER (0 decimals). Yet to now avail.
When I check the spreadsheet, the column has retained the cell formatting, and I have a 'I' pop-up to inform me that 'someone' has inserted text data into the number column (even though the data IS number).
Since the SP spits out INT, it isn't a case of receiving a text value, imho. While trying to change the external column data type in the advanced editor, SSIS is quite happy to let me change the value for the Sales output to DT_I4, apply, and ok. Then, when I open it immedaitely aftgerwards, it has reverted to the DT_WSTR's! AArrgh. If is can't handle it, at least tell me when I try and change it. don't let me change it, and then revert back without telling me! Grumble grumble...
I know this is very simple, and I've done it before, but now I can't remember how to do it and I keep failing to do it correctly. I want to format a 6 digit number like this: 00-00-00. The number is returned from the database as 6 digits, and I just want to add the dashes.
I tried putting ##-##-## in the Format cell in the properties of the textbox (which I thought worked before) but it's not doing the trick. What am I doing wrong here?
i am trying to build a database that contains a coloumn of phone number but i reallly dont know where and how i will be able to give a specific format for phone numbers.. or anything else any common type of information that may have a format..
Will i use Visual Web Developer or SQL Server Management or whatever else to arrange my phone number coloumn?
I'm currently getting a date in this format 2001-10-08 10:35:45 (yyyy-mm-dd hh:mm:ss) how would I convert the date to this format 2001-10-08/10:35:45 (yyyy-mm-dd/hh:mm:ss)? The only thing added was the / between the date and time.
How can I format the current date as yyyy-mm-dd and display it in a text box on the report. The FormatDateTime function only allows certain NamedFormat for the short and long dates but does not satisfy the above requirements. Any ideas?