473,289 Members | 1,729 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

How to Format date without "Convert " function in T-SQL

13
I dont want to use Convert(Char(9),date,106) function to show date in dd/MM/yyyy format. It changes the datatype of my column to char & I cant perform other date operations on it without changing it back to datetime.
Is there any T-sql way of doing it?
Jun 4 '07 #1
6 24288
iburyak
1,017 Expert 512MB
Why would you care what format date has when you do data manipulation.
Use convert function only in select close when you try to display this date to a user.

Expand|Select|Wrap|Line Numbers
  1. SELECT Convert(Char(9),date,106) Date
  2. FROM some_table 
  3. WHERE date > dateadd(d, -10, getdate())
  4. ORDER BY date
Good Luck.
Jun 4 '07 #2
Motoma
3,237 Expert 2GB
You are trying to get the datetime formatted in a non-standard format but still be able to use it in the date?

I would suggest creating a second column with the altered format, use that for viewing, and use the original for altering, updating, and calculating.
Jun 4 '07 #3
A couple of other options:
A bit verbose, but you can do this:

select
dtForm = cast(day(dateField) as varchar) + '/' + cast(month(dateField) as varchar) + '/' + cast(year(dateField) as varchar)

obviously, "dateField" is the name of whatever date field you're pulling from the database. If you still wanted that as a date, you could always recast that back to datetime with the cast function, just like I was doing with the varchar.
Jun 12 '07 #4
If you are doing it in a datagrid, just change the defaultcellstyle of the date column. "d" is the short date format mm/dd/yyyy

Me.dataGridView1.Columns("NameOfDateColumn").Defau ltCellStyle.Format = "d"

The cell will still hold the datetime value from the database, but will only show the converted date to the user.
Jul 6 '07 #5
Why would you care what format date has when you do data manipulation.
Use convert function only in select close when you try to display this date to a user.

Expand|Select|Wrap|Line Numbers
  1. SELECT Convert(Char(9),date,106) Date
  2. FROM some_table 
  3. WHERE date > dateadd(d, -10, getdate())
  4. ORDER BY date
Good Luck.

Excuse me, can you tell me why char(9) is used for an argument for Convert()
Thanks

Dave
Aug 2 '07 #6
this link has the details explanation:
http://www.dreamincode.net/forums/showtopic42872.htm

manpakhong@hotmail.com


Excuse me, can you tell me why char(9) is used for an argument for Convert()
Thanks

Dave
Apr 28 '08 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Westcoast Sheri | last post by:
When I used the "date("nj")" function to generate an invoice, about 1 out of 500 times, nothing comes up <?php $invoicedate = date("nj"); ?> <html><head></head><body> Invoice date: <?php...
4
by: Funnyweb | last post by:
I have just notices that the date() function is not returning the correct date/time on my "server". I am running apache2 on my winxp pro laptop. My system clock is set to the correct date,...
2
by: ethoemmes | last post by:
Hi I am trying to use the below functions to return the month for a given date. I want the month displayed as Jan for 01 so I wrapped the Month function in the Format function. Month:...
4
by: Patrick McGuire | last post by:
I need to get the current date in vb .NET. In vba I always used the Date() function, and the help in .NET indicates that this function still exists, but I can't seem to find it. The Now()...
10
by: KJ | last post by:
Hello, Why does this not work? txtMoney.Text.Format("#,##0.00") If I type 100 in txtMoney, it returns #,##0.00 I have never gotten the format function to work in .NET.
16
by: Al Reid | last post by:
First, I'm using vb2005. I have a string that is read from a barcode reader into a TextBox. The string is 6 characters long and represents a date (mmddyy). I want to display it to the user in a...
4
by: Steve Long | last post by:
Hello, I'm encountering a bug, or user error, with the Format function and I'm wondering if someone can either straighten me out or confirm the bug. I'm passing in a Date to the Format function...
2
by: fniles | last post by:
How do you do Format(Date, "mmddyy") in VB.NET ? Thank you
11
by: walterbyrd | last post by:
My MySQL table has a field that is set as type "date." I need to get today's date, and insert it into that field. The default for that MySQL field is 2006-00-00. I know about the date()...
3
by: murch.alexander | last post by:
I made a simple public function to set and return a date value (see below). I have a number of queries that call up the function to get the "As Of Date," which is typically set to today's date....
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.