convert date to mmddyy/hhmmss format

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)

convert date to mmddyy/hhmmss format

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-21 : 14:30:11

Hi Guys, I am trying to create a record that includes the current date and time in the following format
Date = mmddyy
Time= hhmmss
Note: the date and the time are two seperate fields

I am having a problem getting the getdate function converted. Here is what I have so far:
INSERT INTO dbo.tblHdr (Record_Code, Instiution, BatchDate)
SELECT 'HDAJ','ACSB', DATEPART(DD,(CONVERT(CHAR(6), getdate(), 112)))
CONVERT(CHAR(6), GETDATE(), 108)

How do I get it into the desired format?
I am getting error msg: Incorrect syntax near 'GETDATE'

kiri
Starting Member

38 Posts

Posted - 2008-02-21 : 14:50:07

try like this:

INSERT INTO dbo.tblHdr (Record_Code, Instiution, BatchDate)
SELECT 'HDAJ','ACSB', DATEPART(DD,(CONVERT(CHAR(8), getdate(), 112)))
CONVERT(CHAR(8), GETDATE(), 108)

kiri
Starting Member

38 Posts

Posted - 2008-02-21 : 14:53:04

try:

INSERT INTO dbo.tblHdr (Record_Code, Instiution, BatchDate)
SELECT 'HDAJ','ACSB', DATEPART(DD,(CONVERT(CHAR(6), getdate(), 112))) ,CONVERT(CHAR(6), GETDATE(), 108)

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-21 : 15:00:22

Try this:

INSERT INTO dbo.tblHdr (Record_Code, Instiution, BatchDate)
SELECT 'HDAJ','ACSB', replace(CONVERT(CHAR(8), getdate(), 10), '-', '') + '/' + replace(CONVERT(CHAR(8), GETDATE(), 108), ':', '')

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-21 : 15:50:22

Why not just use the correct data type to store your data and not worry about formatting at all? Or would that be too easy and make too much sense?

- Jeff
http://weblogs.sqlteam.com/JeffS

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-21 : 18:46:15

Thanks so much to everyone for responding. Good point Jeff, but I'm accessing someone else's table and we have not control over the layout.

I'll try your suggestions tomorrow and let you know how things turn out.

Thank you Again,
Trudye

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-22 : 01:06:28

1 Always use proper DATETIME datatype to store dates
2 If you already have varchar column to store dates, then create a new column of DATETIME datatype,copy the values to that column and use that column for furthur manipulations, in case if you dont want to drop varchar column

Madhivanan

Failing to plan is Planning to fail

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-22 : 18:10:09

Thanx to everyone for responding. Special thanx to rmaio your solution worked with a slight variation. I just had to split the answer into two fields. But your solution will come in handy in the very near future.

Thank again everyone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources