Default value getdate() formatting....

[mitchelt]

I need some advice, I thought I was done with this project when I suddenly realized that because I was capturing the User Time (format = HHMMSS) on the form page they were submiting the time would be hours earlier or later than the server time.

I know I can use getdate() as a default value for the field on the SQL Server, but the catch is that it has to be in HHMMSS format and must show leading zeros for hours, minutes and seconds.

Any help or advice would be greatly appreciated.

Mitch

 

[DotNetGnat]

Try this:

Convert(varchar(10), getdate(), 108)

-DNG

 

[amrita418]

Try this:

select right('0' + convert(varchar,datepart(hour, getdate())), 2) + right('0' + convert(varchar, datepart(mi, getdate())), 2)
+ right('0' + convert(varchar, datepart(ss,  getdate())), 2)

Regards,
AA

 

[amrita418]

OR

select stuff(stuff(Convert(varchar(10), getdate(), 108), 3, 1, ''), 5, 1, '')

Regards,
AA

 

[mitchelt]

DNG,

So close, it returned: 18:59:43

Any ideas how to get remove the ":"?

Thanks for responding so quick!

Mitch

 

[mitchelt]

AA,

The code needs to go in the Default Value of the field in Enterprise Manager, I believe the code you provided would be for a select statement?

Thanks,

Mitch

 

[mitchelt]

DNG,

I have to say that I am very surprised that you could enter so much code in a default value.

When I saw your code it dawned on me to use the code that I was using elsewhere that was doing the HHMMSS.

This worked fine:

REPLACE(CONVERT(VARCHAR(10), GetDate(), 108), ':', SPACE(0))

A million thanks for pointing me in the right direction and opening my eyes to the possibilities of what can be entered as a default.

Mitch

 

[amrita418]

Why do you think it you cannot add the code for a default value?

create table #Test (ID int, datevalue char(6) default stuff(stuff(Convert(varchar(10), getdate(), 108), 3, 1, ''), 5, 1, ''))

select * from #Test

insert into #Test(id) values (1)

select * from #Test

drop table #Test

Regards,
AA

 

[amrita418]

Nevermind. I thought you were not sure how to do it.

I was giving you a sample using the select stmt.

Regards,
AA

 

[DotNetGnat]

no problem...glad to be of help...

-DNG