hiii please help me out - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

hiii please help me out

Author

Topic

sarathaluri
Starting Member

6 Posts

Posted - 2007-10-12 : 19:52:26

hii

there are 2 tables .
dbo.SIS_Data and dbo.Venkata.
the fields are
dbo.SIS_Data: dbo.Venkata
SID SID
Fname Fname
MI MI
Lastname Lastname
DOB (declared in datetime) DOB(declared in nvarchar(50))

I am working with sql server 2005.
I need to insert the values of dbo.Venkata into dbo.SIS_Data.
but dob has different data type.
we need to use the convert or cast function.the format of dob in dbo.Venkata is ddmmyyyy and we need to change the format to yyyymmdd in order to insert it into dbo.SIS_Data.

so please help me out with the query.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-13 : 01:14:25

I hate doing the date conversions. Just do a google search on covert datetime to varchar then lookup all the differant formats.


insert into Sis_Data(SID,FNAME,MI,LastName,DOB)
select SID,FNAME,MI,LAstname,convert(varchar(10),DOB,102) 
from Venkata a

As stated lookup all the differant conversions for datetime to varchar, then just modify the above code. It's all over the web along with this forum, but you'll need to do the leg work to code it (It's not difficult just will take a few minutes of your time to look up.)

sarathaluri
Starting Member

6 Posts

Posted - 2007-10-13 : 03:38:47

hii
thanx for ur reply
i tried wid that quey.
finally i tried out the following query
select convert(datetime,stuff(stuff('08162006',30,'/'),6,0,'/'),101)

or else

cast(stuff(stuff('08162006',30,'/'),6,0,'/')as datetime)
actually there aree 15000 entries .
by executing the above queries i got the output as
the same date
20061608 for 15000 times
but i need the dob in the table.....
what can i doooooo

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-13 : 14:59:33

Here's one way assuming your varchar date is always in the format of 2 digit month 2 digit day 4 diget year. If for instance january 1st 2007 was 1012007, rather then 01012007 you would need to pad the below code to add a zero when then length was < 8

Also a datetime column is only 1 format (A datetime). You can change the way it looks, but when you insert you just need to do so in any datetime format. The end result will always be a date time.

(You were mentioning you needed to format it in a specific way for the datetime columntype, but there is no need.)

[code]

declare @Date varchar(50)
set @Date = '28022005'
set @Date = Substring(@Date,3,2) + '/' + LEFT(@Date,2) + '/'+ RIGHT(@Date,4)
declare @Datenew as datetime
set @DateNew = @Date
Select @Datenew

--Now to use this in your query it's simply

insert into Sis_Data(SID,FNAME,MI,LastName,DOB)
select SID,FNAME,MI,LAstname,Substring(DOB,3,2) + '/' + LEFT(DOB,2) + '/'+ RIGHT(DOB,4)
from Venkata a

Kristen
Test

22859 Posts

Posted - 2007-10-13 : 15:40:51

So: you want to copy a date, stored in a nvarchar(50) in dbo.Venkata.DOB in the format ddmmyyyy

into dbo.SIS_Data.DOB which is of data type DATETIME.

Note that you mistakenly think that you need to convert the format to "yyyymmdd" in order to make the Insert. You don't! For a datatype of DATETIME there is no format, the Formatting and the Storage are two completely different things.

A value stored in a DATETIME datatype can be displayed in any desired format; but more importantly it can also be compared, and manipulated, based on chronology.

Goodness only knows how it got to be stored in an Nvarchar(5) ... but that's another story, I'm pleased that you are moving it to a proper DATETIME datatype column.

If you have a string representation of a date you either:

Need to present it to SQL Server in:

yyyymmdd
or
yyyymmdd hh:mm:ss.000

formats, or you need to explicitly provide a conversion from whatever format it is in. (You can also rely on a IMplicit conversion, but that is very risky)

An EXplicit conversion for your scenario is:

insert into Sis_Data(SID,FNAME,MI,LastName,DOB)
select SID,FNAME,MI,LAstname,convert(datetime, STUFF(STUFF(DOB, 3, 0, '/'), 6, 0, '/'), 103)
from Venkata

Test rig:


SET DATEFORMAT DMY
SELECT CONVERT(datetime, STUFF(STUFF('01122007', 3, 0, '/'), 6, 0, '/'), 103) 
SELECT CONVERT(datetime, STUFF(STUFF('31122007', 3, 0, '/'), 6, 0, '/'), 103)
-- Check becomes 01-Feb-2007, and NOT 02-Jan-2007! 
SELECT CONVERT(datetime, STUFF(STUFF('01022007', 3, 0, '/'), 6, 0, '/'), 103)

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-13 : 15:42:35

"Note that you mistakenly think that you need to convert the format to "yyyymmdd" in order to make the Insert"

Sorry, meant to say:

If you present a string date in "yyyymmdd" format that is unambiguous, so converting string-dates in "other" formats to "yyyymmdd" is one way to make them acceptable to SQL Server. I prefer to Explicitly convert them, so that it is obvious that it was known what format they were in ...

Kristen

sarathaluri
Starting Member

6 Posts

Posted - 2007-10-13 : 18:51:08

hii
there are around 15000 entries in dob in dbo.Venkata
but i cant select all of them into dbo.SIS_Data

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-13 : 20:17:43

???

The below query will take all 15000 records and insert them into Sis_Data.

Please clarify.



insert into Sis_Data(SID,FNAME,MI,LastName,DOB)
select SID,FNAME,MI,LAstname,Substring(DOB,3,2) + '/' + LEFT(DOB,2) + '/'+ RIGHT(DOB,4)
from Venkata a

Kristen
Test

22859 Posts

Posted - 2007-10-14 : 00:52:08

That will only work if you have American locale on the SQL Server box. Better to convert the dates to yyyymmdd format which is server-locale neutral, and using an explicit convert to DATETIME won't hurt either!


insert into Sis_Data(SID,FNAME,MI,LastName,DOB)
select SID,FNAME,MI,LAstname,
CONVERT(datetime, STUFF(STUFF(DOB, 3, 0, '/'), 6, 0, '/'), 103)
from Venkata a

"i cant select all of them into dbo.SIS_Data"

Do you mean that some of them are not valid dates? (Common problem when dates are stored as text!)

Try:


SELECT DOB
FROM Venkata
WHERE IsDate(STUFF(STUFF(DOB, 3, 0, '/'), 6, 0, '/')) = 0

Kristen

sarathaluri
Starting Member

6 Posts

Posted - 2007-10-15 : 11:41:27

hiii i need to just covert nvarchar(50) to datetime ..
so can anyone please help me out

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 11:43:34

Either read all the useful information people have given you above, or read up CONVERT in Books Online.

Or wave a Magic Wand I suppose ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources