Convert/Cast Date - 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 2008 Forums

Transact-SQL (2008)

Convert/Cast Date

Author

Topic

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-02-17 : 17:28:09

I need to convert/cast the date you get from getdate() to this exact format:


1/1/2011 4:30:45 PM

....It cannot look like


01/01/2011 04:30:45

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-02-17 : 17:36:07

Why do you need to do this? Normal procedure would have the formatting done at the presentation layer.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-02-18 : 09:29:15

It's because i'm working with old data that i cannot change and i need to try to join based on it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-18 : 09:54:38

What is the datatype of the existing column?

Madhivanan

Failing to plan is Planning to fail

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-02-18 : 10:48:53

the column i created in SQL is varchar and the data is coming from an access database that is in a column with datatype "text".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-18 : 11:06:49

quote:
Originally posted by Brittney10

the column i created in SQL is varchar and the data is coming from an access database that is in a column with datatype "text".

It is better to use datetime datatype in SQL Server and import data from access

Madhivanan

Failing to plan is Planning to fail

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-02-18 : 13:50:53

the data in access and SQL are both varchar/text datatypes because the field is a concatenation of a string and a date.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-18 : 14:09:56

quote:
the data in access and SQL are both varchar/text datatypes because the field is a concatenation of a string and a date.

Does that mean there's non-date data in that column? If so, can you parse out the date and time cleanly?

The advice you're getting (and best you're going to get) is to use the datetime data type, especially if you are going to compare it to GETDATE(). If you can't change the existing column, you can modify the table to add a datetime column and update with with a converted value, or a computed datetime column that generates it on the fly. Either one can be indexed for performance.

This will be a lot easier than trying to turn GETDATE() into a string that you want, especially since you can't guarantee the string data is consistently formatted as a date. You also cannot properly compare string dates for non-equality (greater or less than).

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-18 : 14:18:46

how exactly the data looks like in Access?

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-02-18 : 14:24:08

The data in Access (which i cannot modify) looks like:


SomeNameHere1/7/2011 8:30:33 AM

I need to recreate the date portion of this in SQL, but i can't.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-18 : 14:43:29

quote:
Originally posted by Brittney10

I need to convert/cast the date you get from getdate() to this exact format:

1/1/2011 4:30:45 PM

....It cannot look like

01/01/2011 04:30:45

Seems that the datetime portion is in the same format as you want it to be in SQL Server?? if so then why dont you just separate the datetime portion from this string/text while importing it into the SQL server.... and dump it as it is into the SQL server table

By the way i am afraid you might not be able to use this date (data once imported into SQL server) for searching purposes as you do a normal date. And if you do have need of it then I would advise the approach suggested by Rob

Cheers
MIK

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-18 : 15:07:38

Help us out here:

quote:
i'm working with old data that i cannot change and i need to try to join based on it.

If it's old data in Access, can't you just import it into SQL Server and work with the imported version? If it's truly old data it won't change.

Even if it does change, if it's in Access there can't be too much of it. Can you write a procedure to import, parse/clean the dates, and then do the joins in SQL Server only? You just run it whenever you need to refresh the data.

quote:
The data in Access (which i cannot modify) looks like: SomeNameHere1/7/2011 8:30:33 AM

Or can you find whoever designed it this way and take away their computer?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-18 : 15:39:04

Here's why you don't want to do string manipulations on getdate()

declare @name varchar(20)
declare @date datetime
declare @tmpDate varchar(20)

set @name = 'namehere'
set @date = dateadd(hour,-4,getdate())
set @tmpdate = convert(varchar(20),@date,101)select @name
 +stuff(stuff(@tmpdate,1,1,''),3,1,'')+ ' '  
 +substring(convert(varchar(30),@date,100),12,6)+' '
 + substring((convert(varchar(30),@date,100)) ,18,2)

Jim

Everyday I learn something that somebody else already knew

yuanyelss
Starting Member

4 Posts

Posted - 2011-02-18 : 22:07:37

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources