| Author |
Topic |
|
stormcandi
Starting Member
46 Posts |
 Posted - 2007-04-06 : 12:18:31
|
| Hello everyone,I am relatively new to the functions of SQL Server, however I have looked everywhere for this answer to no avail.I need to take a date from the following format:Apr 3 2007 2:44PMand convert it to this format:4/3/2007 2:44:15 AMI have checked the style codes but I could not find any that would help me do this.Can anyone assist me?Thanks in advance! |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-04-06 : 12:26:17
|
Doesn't this work for you?select convert(Datetime, 'Apr 3 2007 2:44PM')Normally I prefer ISO format - yyyymmdd.Also, do all datetime formatting and conversion at the front-end (© Madhi)  Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
stormcandi
Starting Member
46 Posts |
Posted - 2007-04-06 : 12:30:11
|
| The initial value was a string which I then cast as datetime to use the dateadd() function on. I will try your way and see what happens. Thanks a bunch! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-06 : 12:39:20
|
quote:
I need to take a date from the following format:Apr 3 2007 2:44PMand convert it to this format:4/3/2007 2:44:15 AM
Where does the date in that initial format come from? User input? A parameter? Values in a table?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
stormcandi
Starting Member
46 Posts |
Posted - 2007-04-06 : 13:04:48
|
| It comes from the value in a table. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-06 : 13:13:14
|
| What is the datatype of the column in the table that those values come from? Also, how is that table populated? Is this just a one-time conversion thing, or is will this be on-going?The reason why I ask all this is because if you just use the correct DateTime datatype for everything, date formats is not an issue at all since SQL Server can deal directly with datetime values.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
stormcandi
Starting Member
46 Posts |
Posted - 2007-04-06 : 13:27:35
|
| The datatype of the column is a varchar. It is actually a field that holds the time a user logged in. I have a SQL Job that runs at midnight and searches for any logins without a logout (when it has been more than 5 hours.) If it finds a login more than 5 hours ago it takes the login and adds 5 hours to it and saves it as a logout time. For the fields with a null for the logout time this is just a one-time thing. But there may be fields found everynight that need to be closed out. I saved the data as a varchar because I was having issues whenever I needed to use the value for something else or even to compare it to itself. This was the best way I could think of and up until now, I have not had any issues. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-06 : 13:33:17
|
| Well, if you can, you really, really should simply use the correct and standard DateTime datatype. It is the #1 rule of databases -- use the correct datatypes in the columns. if you experience some sort of problem calculating or storing DateTimes that forces you to use VARCHAR, then you are creating a bigger problem for yourself in order to solve a small one which is probably easily fixed.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
stormcandi
Starting Member
46 Posts |
Posted - 2007-04-06 : 13:43:48
|
| Thanks! I will see about changing it to the correct datatype. That should fix the problem. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
Date Format in SQL