How to have SQL put in a different date 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 2000 Forums

Transact-SQL (2000)

How to have SQL put in a different date format

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-09-18 : 03:18:29

SQL 2000 Server

How to have SQL put in a different date format when using a getdate() function

Field Name: Record_ID
Default Value : getDate()

Whenever a new record is added, the field Record_ID had a format of "Sep 17 2006 11:55AM"

How do I get SQL to use a format of "9-17-2006 11:55AM" instead of "Sep 17 2006 11:55AM"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-18 : 03:27:27

Either this:

select convert(varchar(20), getdate(), 110) + ' ' + convert(varchar(20), getdate(), 108)

or better:

Do the formatting in the front-end

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 03:28:08

select replace(convert(varchar, getdate(), 110) + SUBSTRING(convert(varchar, getdate(), 0), 12, 8), ' ', ' ')

Peter Larsson
Helsingborg, Sweden

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-09-18 : 03:52:04

Hi there gentlemen. I put the entire statement
select replace(convert(varchar, getdate(), 110) + SUBSTRING(convert(varchar, getdate(), 0), 12, 8), ' ', ' ')

in a Default section in the Design table on that field but it does not work. When I added
a new record it gives me an error. ODBC error, truncate.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-18 : 03:55:22

what is the datatype of the column where you are trying to specify default value??

Ideally it should be varchar(19)

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 03:57:01

NOT the entire statement!

replace SELECT with equal sign (=) such as

=replace(convert(varchar, getdate(), 110) + SUBSTRING(convert(varchar, getdate(), 0), 12, 8), ' ', ' ')

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-18 : 04:09:40

Lin100, don't store your date as a varchar, please. Store it in SQL's native DATETIME datatype and then format when you want to display it - preferably in the front end, but if you can't do it there then using the CONVERT function in SQL.

Note that if you send it to the front end as a string, rather than as a date/time datatype, the front end isn't going to recognise it as a date, so its not going to be able to do date-specific-things with it.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-18 : 11:38:19

Why do most of newbies want to store Dates as Varchars?
Use proper DATETIME datatype to store dates and do all formations at front end application. To query against dates, refer this http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-18 : 11:52:41

quote:
Originally posted by madhivanan

Why do most of newbies want to store Dates as Varchars?...

They just don't don't realize that the internal format of datetime has nothing to do with the way it is displayed.

CODO ERGO SUM

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-09-19 : 15:37:54

Hi there everyone. Please allowed me to test what harsh_athalye, Peso and chiragkhabaria
have suggested. After I learned this then I 'll do the better methods, which is what others are suggesting.

1) I changed the data type from nvarchar to varchar

2) I put the select statement in the default section. Saved it. I then copy the text and put it here. Notice that is has extra ' ' ' ' when I pasted here. How does it have
exta spaces when I pasted here ?

('=replace(convert(varchar, getdate(), 110) + SUBSTRING(convert(varchar, getdate(), 0), 12, 8), '' '', '' '')')

3) When I ran the form, the record with the format "Sep 17 2006 11:55AM" did not
appear on the Access form. The query did not see this record.
A record with the format "Sep 17 2006 11:55AM" is wha SQL gives to that record.

4) On the Access form, the field RecordInitiateDate has an imput mask of
"99/99/00;0;_"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-19 : 20:51:50

>> I changed the data type from nvarchar to varchar

Why dont you change it as DATETIME?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources