This blog is subject the DISCLAIMER below.

Friday, August 17, 2007

Solveing DateTime problem (SQL Server)

Are you created a table have a dateTime field before ? Are this field made a problem with your application because both of them have a different format ? if you don't want to change the operating system dateTime format to unify both …..

Read , this article that try to solve this headaches .

By the way , I found two SQL Server functions that may be a way to solve the problem because they provides a number of options you can use to format a date/time string.

  • getdate()

  • convert ( , )

by mixing the usage of both you can get your goal as this :

select convert(varchar, getdate(), 9)

that returns :

Dec 11 2007 11:11:11:111AM

You have many date and time schemes by changing the second parameter in Convert function (use one of the below values to change the format).

Rang (1 to 7 + 10 + 101 to 107 + 110 + 111 ) Date format

Rang ( form 8 + 14 ) Time format

Rang ( 9 or 109 ) DateTime format

Explore this simple Demo :

create table fci_blog_table1
z datetime null
/*Demo1 ,How to insert */
insert into fci_blog_table1 values ( convert(varchar, getdate(), 1))

create proc fci_blog_pro1 /*to make a stored proceure*/
@x int output
select @x = 1
WHILE (@x < style="color: rgb(51, 51, 255);">BEGIN

select convert(varchar, getdate(), @x)
select @x = @x + 1

PRINT 'Have a nice time'

DECLARE @TheCount int
EXEC fci_blog_pro1
@x = @TheCount OUTPUT
Select TheCount = @TheCount

No comments: