SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert/Cast Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Brittney10
Posting Yak Master

USA
153 Posts

Posted - 02/17/2011 :  17:28:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 02/17/2011 :  17:36:07  Show Profile  Reply with Quote
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)
Go to Top of Page

Brittney10
Posting Yak Master

USA
153 Posts

Posted - 02/18/2011 :  09:29:15  Show Profile  Reply with Quote
It's because i'm working with old data that i cannot change and i need to try to join based on it.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 02/18/2011 :  09:54:38  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
What is the datatype of the existing column?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Brittney10
Posting Yak Master

USA
153 Posts

Posted - 02/18/2011 :  10:48:53  Show Profile  Reply with Quote
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".
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 02/18/2011 :  11:06:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Brittney10
Posting Yak Master

USA
153 Posts

Posted - 02/18/2011 :  13:50:53  Show Profile  Reply with Quote
the data in access and SQL are both varchar/text datatypes because the field is a concatenation of a string and a date.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 02/18/2011 :  14:09:56  Show Profile  Visit robvolk's Homepage  Reply with Quote
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).
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 02/18/2011 :  14:18:46  Show Profile  Reply with Quote
how exactly the data looks like in Access?
Go to Top of Page

Brittney10
Posting Yak Master

USA
153 Posts

Posted - 02/18/2011 :  14:24:08  Show Profile  Reply with Quote
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.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 02/18/2011 :  14:43:29  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 02/18/2011 :  15:07:38  Show Profile  Visit robvolk's Homepage  Reply with Quote
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?
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 02/18/2011 :  15:39:04  Show Profile  Reply with Quote
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
Go to Top of Page

yuanyelss
Starting Member

China
4 Posts

Posted - 02/18/2011 :  22:07:37  Show Profile  Visit yuanyelss's Homepage  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000