Date Of Birth Conversion

Apr 18, 2000

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:-

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.

Any help will be appreciated.

View 4 Replies


How To Get Age From Date Of Birth

May 15, 2002

Hello Everyone,

I have a table containing a column "date of Birth" in char datatype, I need to calculate Age for the person from his or her date of birth. how would I be able to do that?

Any help will be appreciated.


View 3 Replies View Related

Selecting Date Of Birth

Jun 14, 2008

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.

View 5 Replies View Related

Birth Date 1900-01-01 00:00:00.000

Apr 12, 2005


How to check the default value set up for datetime field? The reason I am asking is my database

field "birthday" is datetime(8), the default value I set to '' already. Everytime my asp program
have empty birthday or invalid birthdate for this field, the system automaticaly set it to

1900-01-01 00:00:00.000

Do you know what happened?


View 3 Replies View Related

Average Date Of Birth

Jul 8, 2004

Hi, folks.
I have a table with a column Date_of_birth.
I want to evaluate average date_of_birth by grouping on depts.
AVG function doesn't seem to work with datetime col. Plz help.

View 8 Replies View Related

How To Get Date Of Birth And Age In Years?

Mar 27, 2007

I have a table name employee and datetime column named dateofbirth, how can i write a select statement to show their, date of birth and age in years?

This is how i did it, i couldn't finish it, Any feedback would be very thankful.

select name, datebirth, year(dateofbirth)
from employee

View 10 Replies View Related

Get Date Of Birth Query Problem...

Jun 7, 2007

i want to make one SQL Query in that i have problem so please help me
i have three column  ID,Name,DOB
in that i want top 5 name whose Birthday is incomming   days....
reply as soon as..

View 7 Replies View Related

Trigger To Calculate Age From Date Of Birth

Dec 12, 2003

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.

View 11 Replies View Related

Converting Birth Date Format

May 6, 2014

I am trying to convert the birth dated from 19591229 to 12/29/1959 and using below code but its not giving me in that format its printing line 1959-12-29. how do I do this?

bdate=convert(date, p.date_of_birth,101)

View 10 Replies View Related

Formatting For A Birth Date Field??

Sep 16, 2006

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

View 11 Replies View Related

T-SQL (SS2K8) :: Formatting Date Of Birth Using RSA ID Number

Nov 8, 2012

I need creating date of birth using ID number the ouput that im looking is a follows

e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01
e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01

The desired format I need is to take the above and create date of birth with the below format as required by the application used.

01 Jan 1980
01 Jan 2000

View 9 Replies View Related

Creating Birth Date From Day, Month, Year Fields

Nov 14, 2007

I have 3 fields (DOB_YEAR, DOB_MO, DOB_DAY)

They are populated like this (1985, 12, 21)

I would like to take the 3 values of populate one field (BIRTH_DATE) with these combined values to look like this (19851221) BIRTH_DATE is an 8 character field in datetime format.

The code

SET BIRTH_DATE = (dob_year+dob_mo+dob_day)

Returns 6/11/1905 for 1959,11,17

View 5 Replies View Related

Date Function - Conversion Failed When Converting Date And / Or Time From Character String

Mar 18, 2014

I have the following

Column Name : [Converted Date]
Data Type : varchar(50)

When I try and do month around the [Converted Date] I get the following error message

“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”

My Query is

month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]

View 7 Replies View Related

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

Nov 16, 2015

SELECT * ,[Due]
  FROM [Events]
 Where Due >= getdate() +90

This returns the error: Conversion failed when converting date and/or time from character string

Why would this be? How to cast or convert this so that it will work? 

View 24 Replies View Related

Flat File Text Date Conversion To SQL Server Date Comments And Suggestions

Mar 12, 2008

Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -

In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".

1) File Connections Manager Editor
1.1) Within File Connections Manager Editor; -
Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC"
and assign a type to the data type e.g. string[DT_STR]

1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.

2) Derived Column Transformation Editor
2.1) Assign Derived Column Name, e.g.

2.2) Select <add as new column> within Derived Column.

2.3) Enter the conversion Expression, e.g. ; -

Since the above conversion is such a common task I suggest that Service Pack 3 of SQL Server 2005 delivers the following functionality; -


2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.

2.5) Within the Mappings tab of the SQL Destination Editor have; -
Destination Column as INTERCHANGE_NET_APP_DATE.

Please comment on the above, I will then pass on my suggestion to Microsoft.

Thanks in advance,


View 1 Replies View Related

Conversion Of Oracle Date Time To Sql Server Date Time In SSIS

Jun 30, 2007

This is driving me nuts..

I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.

I'm struggling with this for a quite a while and I'm not able to get it working.

I tried the oracle query something like this,



this gives me an output of 20070511 23:06:30:000

the space in MM : SS is intentional here, since without that space it appread as smiley

I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error

The value could not be converted because of a potential loss of data

I'm struck with error for hours now. Any pointers would be helpful.


View 3 Replies View Related

Date Conversion

Aug 6, 2005

i do have date problem in sql server, i m using DD/MM/YYYY date format, & passing it to insert & update stat...& compairing it with data in table, which is not working properly, how to convert dd/mm/yyyy to mm/dd/yyyy or yyyy-mm-dd
hoping for solution soon, thanx
murli ......

View 7 Replies View Related

Date Conversion

Sep 21, 2005

I'm searching on a smalldatetime field in SQL Server so a typical value would be 09/21/2005 11:30:00 AM.  I have a search form which offers the user a textbox to search by date and unless they enter the exact date and time, no matching records are found.  Of course I want I all records for a given day to be returned.  This is how I'm doing it now. Thanks.
Dim dteDate_Requested As String = txtDate_Requested.Text
If dteDate_Requested <> "" Then   strSqlText += " Date_Requested='" & dteDate_Requested & "'"End If

View 5 Replies View Related

Conversion To Date

Feb 17, 2006

HI everyne,
I have a varchar field in one table, which contains data in the form '010706' and I want to convert this to date datatype to 01/07/2006 (Jan 07, 2006). When I just import the data to the other table it gets converted to 7/6/2001, how can I convert it right? Please help.

View 2 Replies View Related

Date Conversion

Mar 19, 2001

Hello All,

I need help in converting a date. What i'm looking for is date in format of mm/yyyy.

Thanks in advance.

View 1 Replies View Related

Date Conversion

Nov 27, 2001

I need to import a text file into a table by using DTS.

How to convert a text date to smalldate type ?


View 1 Replies View Related

Date Conversion

Aug 11, 2003

Hi all

I wonder whether any of you can help me with a bit of code that you may have already had to execute??

I have a SQL database logging activities and a load of information in a mdb file that needs to be imported.

Unfortunately the data in the SQL database is in the format yyyy-mm-dd and the data in the mdb file is in dd/mmmm/yyyy.

When i run a DTS to import the data the new rows are imported as they were YYYY-dd-mm.:mad:

data logging as

imported data from last week arrives as

how can i manipulate the data in SQL to reverse the day and month numbers for Aug 1st to Aug 8th??

I have tried changing the mdb data format but that doesnt make a difference. I dont understand DTS enough to know whether it is possible there :confused: and my SQL skills dont rise to the challenge - yet!! :o


View 2 Replies View Related

Date Conversion

Oct 29, 2003

I have a datetime field in a table and I have to insert this datatime data into antoher table. In my insert statement I convert the datetime field into varchar and then insert it into the second table.

The date field in the original table is : 2/2002/13 3:58:12 PM
but in the destination table i get: 2/2002/13 3:58:00 PM

I lose the seconds in the conversion, i think

Whats the best way to preserve to the datetime field during transfer?


View 3 Replies View Related

Date Conversion Help

Mar 6, 2002

I have one column that is a datetime, and another that is an INT which represents seconds. i cannot figure out how to subtract the seconds from the datetime column. sorry, i'm still kind of new to this TSQL. I get this error:Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

when i try to do this
select dateColumn - IntColumn from Table

so i think there must be a way to make sql know that IntColumn is actually seconds. thanks

View 2 Replies View Related

Date Conversion ?

Jan 19, 2004

Is use this stored procedure.
This is the error mesage: "Syntax error converting datetime from character string"

Please help me !

Alter Procedure "Selectie_Date_Tabel" (@datainceput datetime, @datasfirsit datetime,@Grupa AS nvarchar(20))


set nocount on



DECLARE @keyssql AS varchar(1000)

SET @keyssql = 'SELECT * FROM View2'
+ @NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @Grupa + CHAR(39)
+ @NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @datainceput , 120) + ' AND ' + CONVERT(DATETIME, @datasfirsit , 120) +')'

EXEC (@keyssql)

View 12 Replies View Related

Date Conversion Help

Jan 28, 2004


I have my dates in DB2 source in two formats -

Format 1 - char(5) - Example - 10305. 1 indicates century,03 indicates year and 05 indicates month. The day is not stored. So this is 2003,May 1

Format 2 - char(7) - Example - 1030525. 1 indicates century,03 indicates year, 05 indicates month,25 indicates day. 2003,May 25

I want to convert the above two formats to SQL Server smalldatetime and I only need the DATEPART. The date needs to be in the format mm/dd/yyyy. The default day would be 01 when the day is not specified.

If the format is 00305 then the 0 indicated 19th century. So this is 1903, May 1.

Any help is appreciated.



View 7 Replies View Related

Date Conversion

Apr 25, 2008

Hi am trying to convert my date from the date times stap to
this format 250408

I've tried this : select convert(varchar,getdate(),112)
but the result am getting is '20080425'

View 3 Replies View Related

Sql Date Conversion

Jun 15, 2006

priya writes "select convert(smalldatetime,'09/06/2006') is working

select convert(smalldatetime,'13/06/2006') it shows an error
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

why it shows an error"

View 1 Replies View Related

Date Conversion

Feb 6, 2008

I have a SQL database where the dates appear in the format "733433".
If you convert in the "Select" statement, it's fine. You can use the day, month, year concatenated in an excel expression, converts fine. As an expression in reporting services, I receive an error. An help is appreciated.

View 1 Replies View Related

Date Conversion

Apr 4, 2006

I need help with date conversion from character data. In SQL 2000 we used a Date Time Conversion task

I do not see how to do this in SQL 2005 SSIS. I tried a data conversion task to a database timestamp and this is what I got:

[Data Conversion [383]] Error: Data conversion failed while converting column "date_time_stamp" (47) to column "Copy of date_time_stamp" (396). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Here is a sample of the input data I'm trying to convert.

input data example - 2006-03-07-14.42.34

Any ideas? .

View 6 Replies View Related

Date Conversion

Mar 16, 2006


My source is flat file and my destination is SQL SERVER 2005 using SSIS TOOL.

In my source file i got a date column which is in ISO standards ex: 20050131

I have taken source flat file data type as database date [DT_DBDATE] and in

destination table i declared data type as datetime.

When i start debugging i am getting an error saying that data conversion is not possible.

Can you please help me out how to solve the problem, what data types do i need to take in source and destination and is there any necessity of using Data Conversion Transformation.

If, so please tell me how to do.

With Regards

Satish D

View 1 Replies View Related

Date Conversion And Expressions

Aug 31, 2006

I have a table with about 20,000 records that have a date field, stored as a datetime in the database like '8/28/2006 8:42:14 AM'. The dates range from March 2004 to current. What I would like to do is retrieve the dates in that format (month year) and put them in a dropdown. I have this so far:SELECT DISTINCT DATEPART(month, dte_date) AS Expr1, DATEPART(yyyy, dte_date) AS Expr2 FROM myTable ORDER BY DATEPART(yyyy, dte_date), DATEPART(month, dte_date)And the query returns the information that I want, but I can only bind one field to the dropdown. I was thinking that if I return the results a single expression (concantenate?) then I could bind that to the dropdown. I'm not sure as how to go about this. Also, the month returned is numeric and I would rather have the name of the month returned (like "July" instead of "7"). Thanks in advance to anyone who helps me.

View 4 Replies View Related

Trouble With Date Conversion

Jan 8, 2008

I'm trying to insert a date, I don't want the current time. It would be ok if it was 12:00:00. I have tried using the below bold area, but I get an error that the conversion is not correct.
Any ideas?
INSERT INTO tblpayments (rec_ID,client_ID,tranDate,tranAmount,DateEntered,EnteredBy)
SELECT rec_ID,client_ID,Convert(varchar(5), GETDATE(), 10) AS TranDate,Tranamount,DateEntered,EnteredBy

View 1 Replies View Related

Copyrights 2005-15, All rights reserved