Varchar field to Datetime...? - 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)

Varchar field to Datetime...?

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-03-28 : 11:12:17

HI I have a date field which has been imported as a varchar.

I.e
Doc_date
25/03/2007
00/00/0000
etc...

As you can see the data entered did not have very good validation.

How can I convert the dates into the following format?

Doc_date
25/03/2007 --> 25 Mar 2007
00/00/0000 --> blank or null....

many thanks!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 11:16:36

SELECT CONVERT(VARCHAR(11), NULLIF(Doc_date, '00/00/0000'), 106)
FROM Table1

Peter Larsson
Helsingborg, Sweden

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-03-28 : 18:10:44

But that doesn't get it in the format:

25 March 2007 from 25/03/2007.

Is there any way to do this?

Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-28 : 19:17:12

why do you want to convert from string to string ? why not convert from string to datetime ?

convert(varchar(20), convert(datetime, nullif([date], '00/00/0000'), 103), 106)

the title of this thread said "Varchar field to Datetime". !
What you should be doing is only these


convert(datetime, nullif([date], '00/00/0000'), 103)

converting from string to datetime.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 00:55:34

In your ORIGINAL POSTING, you wanted 25 Mar 2007.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-02 : 03:11:22

quote:
Originally posted by dnf999

But that doesn't get it in the format:

25 March 2007 from 25/03/2007.

Is there any way to do this?

Thanks in advance.

Where do you want to show formatted dates?
If you use front end application, use format function there

Madhivanan

Failing to plan is Planning to fail

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-04-02 : 18:44:58

I am exporting the results from sql to excel. Unfortunately, I do not work in an environment where data is taken from a highly validated front end....:(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources